Spliting the contents of a cell to other cells

[Guest]

Hello,
I have a formula/function question. Say I have a cell, For this question
I'll Call it Cell A1 and in that cell the is a list of product numbers
312.00/ 541.0, 541-100\ the only common separator is a space between each
number, and the numbers range between 4 and seven digits. Is the a formula of
function that would separate the product number into three different columns?

Thanks,
Lime

 

[Guest]

check out <Data><Text to columns>

 

[Richard Buttrey]

Hello,
I have a formula/function question. Say I have a cell, For this question
I'll Call it Cell A1 and in that cell the is a list of product numbers
312.00/ 541.0, 541-100\ the only common separator is a space between each
number, and the numbers range between 4 and seven digits. Is the a formula of
function that would separate the product number into three different columns?

Thanks,
Lime

The following splits the 23 character A1 cell into 8, 7 & 8 characters
in B11.


B1 = =LEFT(A1,FIND(" ",A1))

C1= =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1))))

D1= =RIGHT(A1,LEN(A1)-FIND(" ",A1,LEN(B1)+LEN(C1)))

This includes the comma in the middle which you may want to avoid. In
which case change C1 to:
=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("
",A1)-2)))

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Thank you very much guys, you've been very helpful as usal,.

Thanks,
Lime

 

[swatsp0p]

On the main menu, click Data>Text to Columns, choose Delimited, clic
Next, choose 'Space' and click Finish.

Your data should now be three columns.

Good Luc

 

[Guest]

This does not work when you have mulitpal rows containg numers with different
lenths

 

[swatsp0p]

As long as each has a SPACE between them, Text To Columns WILL work.
Follow these instructions:

Select the range of data
On the main menu, click Data>Text to Columns...
In the box that opens, make sure "Delimited" is selected as "Origina
data type"
Click Next>
In the Delimiters section, Uncheck all EXCEPT "Space" (if you want t
eliminate the commas or slash, you can list those as well (you can hav
only one 'other' delimiter-you could rerun TTC to eliminate th
backslash))
Click Next>
Verify the Destination meets your needs
Click Finish

Regardless of the number of characters between spaces, each space wil
begin a new column.

Good Luc