Split multiple lined cell in a different cell

dashing_peep08

Board Regular
Joined
Jul 3, 2007
Messages
75
Hi Guys,
Please help me. I have this query in excel that I cant figure out. I have one cell with a telephone number in there. So happened they contain more than one telephone number written as this. it shows as two or three lines in excel since the user used the Alt Ent Function to makes it multiple lined. How to i separate them in such a way that if there are more than one telephone number it will be displayed in two or more diffrent cells horizontally. In effect, a new column should be created depending on the lines. I tried text to column but dont know how that Alt Ent will be read as delimiter or maybe it's not possible in text to column function.

Thanks please help me.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
You can do this with text to columns. Select the range and call the dialog with Alt+D+E and select the Delimited option button.

In Step 2 of that wizard, click your mouse into the box to the right of the Other option. In that box press Alt+010 and then click the Finsih button.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
You can use Text To Columns... select file type as Delimited on the first Step and in the second Step click into the field next to Other and press CTRL+J... then continue normally.
 

dashing_peep08

Board Regular
Joined
Jul 3, 2007
Messages
75
wow that was both superb... i undertand Ctrl J is justify. but TOm, why Alt+010. that really amazed me. is this one of the unknown codes in Excel. is there a list of that?

many many thanks to you both...
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
By typing ALT + 010 in other, you are using the ASCII equivalent of ALT + Enter

The number 10 is the ASCII code for the newline character
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
i undertand Ctrl J is justify
No, not justify... CTRL+J is the keyboard keystroke for a Line Feed character (ASCII code 10), also know as a newline character, for data entry fields that recognize the old "dumb terminal" (and even older teletype) control characters.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,835
Messages
5,627,158
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top