Find the first letter in a cell

[Corne]

In one column I have a text contaning numbers and letters e.g.:
10S1
143W1
1A9
etc.
I want a second column containing only the first letter of these text. This
letter can be found anywhere between the 1st and 5th position in the cell and
can be any capital letter.

Can anyone help me with this?

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"


=MID(A1,COUNT(1*MID(A1,ROW($1:$9),1)),1)

If this post helps click Yes

 

[BSc Chem Eng Rick]

Corne, Jacob's Soln above is the best but here is one that doesn't need to be
entered as an array. You can also drag it down/copy it to any other cell.

=IF(ISERROR(VALUE(MID(A1,1,1))),MID(A1,1,1),IF(ISERROR(VALUE(MID(A1,2,1))),MID(A1,2,1),IF(ISERROR(VALUE(MID(A1,3,1))),MID(A1,3,1),IF(ISERROR(VALUE(MID(A1,4,1))),

 

[Dave Peterson]

This will return the first non-numeric character (not always a letter) in A1:

=MID(A1,MATCH(FALSE,
ISNUMBER(-MID(A1&"-",ROW(INDIRECT("1:"&LEN(A1&"-"))),1)),0),1)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The extra "-" is just in case the cell contains nothing but digits.

 

[Ron Rosenfeld]

In one column I have a text contaning numbers and letters e.g.:
10S1
143W1
1A9
etc.
I want a second column containing only the first letter of these text. This
letter can be found anywhere between the 1st and 5th position in the cell and
can be any capital letter.

Can anyone help me with this?

This will return the first capital letter in the string, provided it occurs, as
you write, in the first five characters:

=MID(A1,MATCH(1,(CODE(MID(A1,{1,2,3,4,5},1))>=65)*(CODE(MID(A1,{1,2,3,4,5},1))<=90),0),1)

--ron

 

[Ron Rosenfeld]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"


=MID(A1,COUNT(1*MID(A1,ROW($1:$9),1)),1)

If this post helps click Yes

This seems to fail with certain combinations:

10S11
A143
1A99

--ron