Find the first letter in a cell

  • Thread starter Thread starter Corne
  • Start date Start date
C

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?
 
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
 
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))),
 
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.
 
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?
Click to expand...

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
 
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
Click to expand...

This seems to fail with certain combinations:

10S11
A143
1A99

--ron
 
Back
Top