How can I separate two separate lines in a column into two columns?

JakeRex · November 30, 2021, 7:59pm

I searched for 10 minutes for similar questions but couldnt find anything similar to mine.
I have a spreadsheet with around 900 rows of data and about 6 columns that contain customer information.
There are two columns, one for landline numbers and one for mobile numbers. However, on export, for some reason, the system took all the mobile numbers and put them in the same column as the landline numbers and underneath them. Image of sample below. (personal info removed)

My issue is that the mobile number column is now empty.
For clarification, not every customer has a mobile number, but I don’t know how to select and move all the mobile numbers back into their respective column.

Any help would be appreciated.

Hrbrgr · November 30, 2021, 8:14pm

Please provide a fictitious sample file.
Make sure that the separation (space or otherwise) between the Phone and Mobile numbers is visible. And is this separation the same in all lines?

A screenshot is not very helpful in this case. Thank you.

JakeRex · November 30, 2021, 8:34pm

Hi there. I created a sample file for you.
It seems that the Landline and Mobile numbers are separated in the input by a ‘Shift+Enter’.

Help Example.xlsx (6.1 KB)

Villeroy · November 30, 2021, 8:26pm

Line #1

=LEFT($C2;IFERROR(FIND(CHAR(10);$C2)-1;LEN($C2)))

Line #2

=MID($C2;IFERROR(FIND(CHAR(10);$C2)+1;1);LEN($C2))

JakeRex · November 30, 2021, 8:37pm

Hey VIlleroy. Thanks alot for answering.
In the case of Line #2 function for example, is this designed to move all the mobile numbers into a new column? When I try it, it only outputs the mobile number from the first cell in the column

Villeroy · November 30, 2021, 9:12pm

Split_Lines.ods (12.9 KB)

eArmin · May 3, 2024, 1:38pm

Thanks. It worked perfectly for me.

JakeRex · November 30, 2021, 9:23pm

Hey guys. I found the answer.

The data in the phone numbers field was separated by a ‘Carriage Return’.
the representation of a carriage return is \n
I then highlighted the Phone numbers column, went to Find and Replace and in the Find bar entered \n and in the replace box i entered a comma.
Then I highlighted the column again, went to Data > Text to columns, and in the separator options enabled “Separated by” and checked the comma box.

I hope this helps someone in the future. Make sure to have an empty column to the right of the one you’re separating. Cheers everyone.