To extract texts from a cell

[Dewdrop]

Dear friends,

I want to extract texts from a series of data. However I couldn't use Left(), Mid() or Right() due to these data are not in standard format. Is there a more efficient way of doing it other than extracting them manually?

please see attached.

Grateful if anyone can help.

Regards

[ChemistB]

This works for all the examples you gave, in B5 copied down

=IF(AND(CODE(MID(A5,1,1))>=65, CODE(MID(A5,1,1))<=90),MID(A5,1,SEARCH(".",A5)-1),MID(A5,SEARCH(".",A5)+1,SEARCH("^", SUBSTITUTE(A5,".","^",2))-SEARCH(".",A5)-1))

[Glenn Kennedy]

A nice solution, ChemistB.

[ChemistB]

Thank you Glen.

[Dewdrop]

Hi ChemistB,

wow, it works. i have no clue what's the formula is, is complicated for me but it's what i was looking for.

thank you so much.

[martindwilson]

another
=TRIM(IF(ISNUMBER(LEFT(A5)+0),MID(SUBSTITUTE(A5,".",REPT(" ",25)),20,15),LEFT(SUBSTITUTE(A5,".",REPT(" ",25)),20)))

[Dewdrop]

Hi Martindwilson,

Thanks for sharing, it works exactly what i want. Fantastic!