Multiple text-number in cell divider formula?
 

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

Multiple text-number in cell divider formula?
 

On Tue, 18 May 2010 13:37:01 -0700, ekonomija
wrote:

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

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&"123456 7890"))-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