regex - Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC). Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded number that the caller was forwarded to.("1 (4xx) 8xx-3xx2 1 (5xx) 4xx-9xx1")

So does column D have two sets of data - the date and time of the call".

In the LC cell content box on the bar it only displays one number which is the first number "1 (4xx) 8xx-3xx2" only.

I need to delete the second number in all cells in column C so I can do a sort of the data yet cannot separate these two numbers or see the second number on the bar.

Need to know how many times a telephone number came into the helpline.

Any ideas?

Assuming the data starts in row 2 and E is first free column (if not, shift appropriately), do:

• In cell F2, press Ctrl-Enter to enter a newline.
• In cell E2, enter formula =LEFT(C2, FIND(CHAR(10), C2))
• Copy E2 to E2..En where n is the last line.

That should give you the first line of each value.

Getting the second line is a little bit trickier as you need to calculate the number of characters. I think the formula is =RIGHT(C2, LENGTH(C2) - FIND(CHAR(10), C2) - 1).

Note: updated to use CHAR(10) as suggested by tohuwawohu.

There are two ways to make the line break accessible for LibreOffice Calc text functions:

• using regular expressions: "\n" will match a line break if regular expressions are enabled in formulas (Menu "Tools" -> "Options" -> "LibreOffice Calc" -> "Calculate" -> "Enable regular expressions in formulas");

• without regular expressions, using the CHAR() function: "CHAR(10)" matches a line break, too.

Based on this, it's possible to break up cell content (in C1) on a line break using the LEFT() and RIGHT() functions in two ways:

• with regular expressions, you can determine the position of the line break using the SEARCH() function:

  =LEFT(C1;SEARCH("\n";C1)-1)

  =RIGHT(C1;(LEN(C1)-SEARCH("\n";C1;1))+1)

• without regular expressions, you can determine the position of the line break using the FIND() function:

  =LEFT(C1;FIND(CHAR(10);C1)-1)

  =RIGHT(C1;(LEN(C1)-FIND(CHAR(10);C1;1))+1)

NB: To be precise, SEARCH() works with CHAR(10), too, but not vice versa - FIND() tries to match a literal "\n" instead of a line break.

NB2: The +1 and -1 respectively make sure that the resulting string doesn't contain the line break.