Separating 2 lines of text within a cell

Hi, I'm wondering if there is a formula I can do in Excel to separate two lines of text that are currently within a single cell. The second line of text was originally input using the control/command key, and there are multiple words with different character lengths within each line. I need the second line of text to be cut and pasted into a separate adjacent cell so that I can convert my spreadsheet (a huge address list) into a .csv file for a mail merge.

If there is a way to do this with a formula, please PLEASE share. Thanks!

Examples of text within a single cell:

Ms. Jane Doe
Mr. John Smith

1 North Avenue
Apartment 2

A1: Text string
B1: =LEFT(A1,FIND(CHAR(10),A1)-1)
C1: =MID(A1,FIND(CHAR(10),A1)+1,LEN(A1))

Thank you so much, that worked. One question - Is there a way to make the B1 formula conditional, so that if cells did not have a second line of text (which is the case for about 2/3 of my spreadsheet), the first and only line of text would still paste into B1 rather than "#VALUE!"?

Originally Posted by katsmeow624

Is there a way to make that formula conditional, so that if cells did not have a second line of text

A1 = text string
B1: =IF(ISNUMBER(FIND(CHAR(10),A1)), LEFT(A1,FIND(CHAR(10),A1)-1), A1)
C1: =IF(ISNUMBER(FIND(CHAR(10),A1)), MID(A1,FIND(CHAR(10),A1)+1,LEN(A1)), "")

I just hopped on my MacBook to edit the Excel file there, and the formula doesn't work

LEFT is a function in Excel Mac, too. Perhaps the CHAR() function isn't a MAC function? I couldn't comment. Try putting =CHAR(10) in a cell and see if you get an error.

Originally Posted by JBeaucaire

A1 = text string
B1: =IF(ISNUMBER(FIND(CHAR(10),A1)), LEFT(A1,FIND(CHAR(10),A1)-1), A1)
C1: =IF(ISNUMBER(FIND(CHAR(10),A1)), MID(A1,FIND(CHAR(10),A1)+1,LEN(A1)), "")
]
LEFT is a function in Excel Mac, too. Perhaps the CHAR() function isn't a MAC function? I couldn't comment. Try putting =CHAR(10) in a cell and see if you get an error.

THANKS! That worked perfectly on the PC version but not on the Mac.

On the Mac, =CHAR(10) results in a blank text box. The new conditional formulas caused the text in column B to look exactly the same as column A, except there is a space between the two lines of text and no second line. And column C is blank. Any idea why?