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
---------------
Jacob Skaria


"Corne" wrote:

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?

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(ISE RROR(VALUE(MID(A1,2,1))),MID(A1,2,1),IF(ISERROR(VA LUE(MID(A1,3,1))),MID(A1,3,1),IF(ISERROR(VALUE(MID (A1,4,1))),

--
If this helps, please click "Yes"
<<<<<<<<<<<


"Corne" wrote:

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 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.




Corne wrote:

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?

--

Dave Peterson

On Mon, 26 Oct 2009 07:44:01 -0700, Corne
wrote:

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

On Mon, 26 Oct 2009 07:51:01 -0700, Jacob Skaria
wrote:

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
---------------
Jacob Skaria

This seems to fail with certain combinations:

10S11
A143
1A99

--ron