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

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

Resources