Forum Discussion

Copper Contributor

Oct 06, 2017

Having trouble splitting data in 1 cell

I have multiple lines of data that is in 1 cell that I would like to 'Text to Columns'. Every time that I try to separate it the lower half of the data disappears. This is what it looks like: ...

FL LTC Pharmacy.xlsx21 KB

excel

Excel on Mac

SergeiBaklan

Diamond Contributor

Oct 06, 2017

Hi,

First replace in your text line breaks on semicolons - open Find & Replace; stay on Find what: bar and press Ctrl+j. After that semicolon in Replace with.

Now Text to Columns with semicolon delimeter

null null

Copper Contributor

Oct 06, 2017

Hi Sergi,

When I am on the Find Bar and I hit Ctrl+j the computer just beeps.

Ron

SergeiBaklan
Diamond Contributor
Oct 06, 2017
Just recognized you may do that in one step - in Text to Columns select two delimeterts - semicolon and Other. Into the box near Other enter Ctrl+j

But all that is for Windows. Try to check if Ctrl+j works on Mac as well
- SergeiBaklan
  Diamond Contributor
  Oct 06, 2017
  On Windows the shortcut which inserts new line when you edit the cell (Alt+Enter) also works instead of Ctrl+j.
  
  On Mac that is
  Insert a line break in a cell ..... or CONTROL + OPTION + RETURN
  https://support.office.com/en-us/article/Keyboard-shortcuts-in-Excel-2016-for-Mac-acf5419e-1f87-444d-962f-4e951a658ccd
  
  Try it
  
  Sorry, never had Mac.
SergeiBaklan
Diamond Contributor
Oct 06, 2017
Not sure about the beep, but you will see nothing here - Ctrl+j adds invisible character here (line break). It looks like this
Please be sure you enter Ctrl+j only once (otherwise few line breaks will be here). Better to clean the bar (Backspace) before you enter here