Help to extract a letter from a string

[poli_ga]

Hello

I have exported data from a database into a comma separate file and imported the data into a Excel spreadsheet successfully.
But I run into a problem when i want to extract a letter from a text string in Excel. The string is mixed text and numbers, but there is always only one letter. Here is an example of what I would like to do.
In Cell A1 the value is "E-10" i want it to return a "E" in Cell B1
In Cell A2 the value is "A12" I want it to return a "A" in Cell B2
In Cell A3 the value is "09C" I want it to return a "C" in Cell B3
In Cell A4 the value is "g" I want it to return a "g" in cell B4
In Cell A5 the value is "11-S" i Want it to return a "S" in Cell B5
and so on
If the string always had the same order, then i could use Left, Right or MID formula, but I don't know how to deal with the problem when all the values are not in the same order.
Hope you can see what I mean.
Any help would be appreciated
Thanks

[tigeravatar]

poli_ga,

Two ways...

With this array formula in cell B1 and copied down (Note: Array formulas must be confirmed Ctrl+Shift+Enter and not just Enter):

Please Login or Register  to view this content.

With this regular formula in cell B1 and copied down:

Please Login or Register  to view this content.

[Cutter]

I was going to suggest - IF your samples are indicative of all values in that the letter is at either end of the string then:
=IF(NOT(ISNUMBER(LEFT(A2,1)*1)),LEFT(A2,1),IF(NOT(ISNUMBER(RIGHT(A2,1)*1)),RIGHT(A2,1),""))

[Paul]

Hi Poli_ga, welcome to the forum.

Just a note to Tiger's formulas above. If you have any symbols other than "-" in your data, the first formula will return that symbol if it occurs before the letter, since a symbol is a non-numeric character. The second formula, which specifies the letters of the alphabet, should work regardless.

[Paul]

For grins, if you wanted an array formula that works for strings that contain symbols (not sure why, haha), this appears to do so:

=MID(A1,MATCH(1,(CODE(MID(UPPER(A1),ROW(INDIRECT("1:99")),1))>=65)*(CODE(MID(UPPER(A1),ROW(INDIRECT("1:99")),1))<=90),0),1)

[poli_ga]

Hello everyone


First, thank you for the warm welcome, this was absolutely the right place to ask for help. I used the second suggestion that tigeravatar wrote in his reply, and it worked like a charm.

Thank you very much for helping me.