Multiple text-number in cell divider formula?
in [Functions]
|
Prev: Write a sum formula to determine the total of all the numbers bel
Next: Pivot Item Displaying Incorrect Value?
From: ekonomija on 18 May 2010 16:37 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
From: Ron Rosenfeld on 18 May 2010 17:33
On Tue, 18 May 2010 13:37:01 -0700, ekonomija <ekonomija(a)discussions.microsoft.com> 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&"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 |