Spliting the contents of a cell to other cells
 

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

check out <Data<Text to columns

"Lime" wrote:

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

On Thu, 13 Oct 2005 11:39:06 -0700, "Lime"
wrote:

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 B1:D1.


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
__________________________

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

Thanks,
Lime

"Richard Buttrey" wrote:

On Thu, 13 Oct 2005 11:39:06 -0700, "Lime"
wrote:

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 B1:D1.


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
__________________________

On the main menu, click DataText to Columns, choose Delimited, click
Next, choose 'Space' and click Finish.

Your data should now be three columns.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475981

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

"swatsp0p" wrote:


On the main menu, click DataText to Columns, choose Delimited, click
Next, choose 'Space' and click Finish.

Your data should now be three columns.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475981

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 DataText to Columns...
In the box that opens, make sure "Delimited" is selected as "Original
data type"
Click Next
In the Delimiters section, Uncheck all EXCEPT "Space" (if you want to
eliminate the commas or slash, you can list those as well (you can have
only one 'other' delimiter-you could rerun TTC to eliminate the
backslash))
Click Next
Verify the Destination meets your needs
Click Finish

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

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475981