Separate information in single cell into multiple column

[Charles Toh]

Dear all,

I need help. How can i separate the customer information in the single cell into multiple columns? or how to add comma then using text to column. below is the example.

Single cell (A1)

Winsland Pte Ltd
3 bukit bakri
5th storey
Winsland house 1
Singapore
239219
Mr Steven Chua
736 3422

Output

B1 = Winsland pte Ltd
C1= 3 bukit bakri
D1= 5th storey
E1= Winsland house 1
F1= Singapore
G1= 239219
H1= Mr Steven Chua
I1= 736 3422

Thanks

Charles

 

[poolhall]

In Find and Replace dialog, put the cursor in Find and type on the numpad while holding Control: 010

Replace it with comma. Then run a regular text-to-columns routine.

 

[AlphaFrog]

Put this in cell B1
=SUBSTITUTE(A1,CHAR(10),",")
...and copy it down column B for each entry in column A

• Select column B and copy
• With Column B still selected, Select Edit\Paste Special: Values
• This will replace the formulas in column B with the comma delimited text.
• Now you could use Text to Columns on column B

 

[Charles Toh]

Put this in cell B1
=SUBSTITUTE(A1,CHAR(10),",")
...and copy it down column B for each entry in column A

• Select column B and copy
• With Column B still selected, Select Edit\Paste Special: Values
• This will replace the formulas in column B with the comma delimited text.
• Now you could use Text to Columns on column B

Hi Alpha,

Genius... Work great for me.
Thanks.

 

[poolhall]

I guess he had a laptop with no numpad

 

[AlphaFrog]

...type on the numpad while holding Control: 010

I think it should be Alt and not Control.

 

[poolhall]

you're absolutely right, it is of course Alt, not Ctrl