WITHIN CELL, PARSE END OF TEXT, START OF NUMBER

  • Thread starter Thread starter ORLANDO V
  • Start date Start date
O

ORLANDO V

Any suggestions on parsing data as below. I have a long list.
The one thing they all share in common is that they begin with text and end
with two numbers. Spaces can be anywhere within.

Accor SA France 3,300,000 177,214,917
 
If your data is in Col A
then enter this in B1
=MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$255),1)),0)
and press CTRL-SHIFT-ENTER

this will give you the position of first number in the string

Then you can use
=Left(A1,B1-1) to get the text string

=MID(A1,B1,Len(A1)-B1+1) to get the numbers

You can then split the numbers on the blank in between by Data->Text to
columns
 
See one answer in my reply to your prior post...

Please don't post multiple times - it just tends to fragment any answers
you get and to waste the time of those answering posts that already have
replies.
 
Back
Top