Multiple text-number in cell divider formula?

  • Thread starter Thread starter ekonomija
  • Start date Start date
E

ekonomija

Dear,

Is it possible in Excel to divide multiple text and numbers in the separate
columns.

Text in one and two numbers in separate
For example:

Jane & Joe 345 329 (in 3 columns)
X-files 32 344


Thank you very much for help and support!

Kindly,
ekonomija
 
Dear,

Is it possible in Excel to divide multiple text and numbers in the separate
columns.

Text in one and two numbers in separate
For example:

Jane & Joe 345 329 (in 3 columns)
X-files 32 344


Thank you very much for help and support!

Kindly,
ekonomija
Click to expand...

Are your strings always set up as your examples?

In other words, are there ever any numbers within the TEXT portion?
Is the text portion always followed by exactly two space-separated numbers?

If so, then with your full string in A1:

B1:

=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-2)

C1:

=LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198)),
FIND(" ",TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),198)))-1)

D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))


--ron
 
Back
Top