Exctracting comma seperated data from a single cell

**guyvanzyl** · 06-30-2006, 04:03 AM

I want to know how to autopopulate multiple cells from comma seperated data that is pasted into a single cell e.g. a csv file

1,2,3

Rob 1
John 2
Steve 3

Franz Verga · 06-30-2006, 04:30 AM

Nel post news:[email protected]
*guyvanzyl* ha scritto:

> I want to know how to autopopulate multiple cells from comma seperated
> data that is pasted into a single cell e.g. a csv file
>
> 1,2,3
>
> Rob 1
> John 2
> Steve 3

Excel is able to open csv file, so you should have no problem, but if you
have, you have to way to solve:

1) Before opening your file, change the extension of file to .txt and open
it from Excel (menu File, Open, Files of type choose Text files, select your
file, Text Import Wizard will start), and choose Delimited at first step of
Text Import Wizard and Comma as Delimiters at step 2;
2) With your file opened, use Text to Columns (Menu Data, Text to Columns)
and choose Delimited at first step of Text to Columns Wizard and Comma as
Delimiters at step 2.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

**guyvanzyl** · 06-30-2006, 05:20 AM

Thanks Franz, I will give it a go. I have used the text to columns function but it auto populates the data across 3 columns rather than in selected cells. Here is a layout that probably makes more sense

A B
1,2,3,4

A 1
B 2
C 3
D 4

I want the values in A1 (1,2,3,4) to auto populate into B3, B4, B5,B6

CLR · 06-30-2006, 07:20 AM

With your string of 1,2,3 in A1, use these formulas

In B1 =LEFT(A1,FIND(",",A1,1)-1) returns 1
In B2 =MID(A1,FIND(",",A1,1)+1,FIND(",",A1,FIND(",",A1,1))-1) returns 2
IN B3 =MID(A1,FIND(",",A1,FIND(",",A1,1)+1)+1,99) returns 3

hth
Vaya con Dios,
Chuck, CABGx3

"guyvanzyl" wrote:

>
> I want to know how to autopopulate multiple cells from comma seperated
> data that is pasted into a single cell e.g. a csv file
>
> 1,2,3
>
> Rob 1
> John 2
> Steve 3
>
>
> --
> guyvanzyl
> ------------------------------------------------------------------------
> guyvanzyl's Profile: http://www.excelforum.com/member.php...o&userid=35927
> View this thread: http://www.excelforum.com/showthread...hreadid=557212
>
>