Spliting the contents of a cell to other cells

[Lime]

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

[swatsp0p]

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

Your data should now be three columns.

Good Luck

[bj]

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

[Richard Buttrey]

On Thu, 13 Oct 2005 11:39:06 -0700, "Lime"
<[email protected]> 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
__________________________

[Lime]

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"
> <[email protected]> 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
> __________________________
>

[Lime]

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

"swatsp0p" wrote:

>
> On the main menu, click Data>Text 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
>
>

[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 "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