Waht Formula To Use To Show Content Of A Cell - First Line ONLY?

[Dhira]

Cell B1 has:

New York
Los Angeles
Las Vegas
​

Cell A1 needs to only show

New York (first line of Cell B1)
​

I tried: =SUBSTITUTE(Len(B1,"%Chr(13)&Chr(10)%",""))
Basically I need to NOT copy anything after the first carriage return in the cell..
Input appreciated.

 

[njimack]

Excel Workbook
	A	B
1	New York	New YorkLos AngelesLas Vegas
Sheet1

 

[Ruddles]

They're not carriage returns, they're line feeds:-

=LEFT(B1,FIND(CHAR(10),B1&CHAR(10)))

 

[Dhira]

Thanks, njimack and Ruddles.

Works perfect. Appreciate it.

 

[mikerickson]

You should probably do both CHAR(10) and CHAR(13) since Windows uses one and Mac the other.

=LEFT(LEFT(B1,FIND(CHAR(10),B1&CHAR(10))),FIND(CHAR(13),LEFT(B1,FIND(CHAR(10),B1&CHAR(10)))&CHAR(13)))

 

[Ruddles]

Thank you for correcting my correction - knowledge filed away for future use!

Thinks (out loud)... maybe also:-

 =LEFT(B1,MIN(FIND(CHAR(10),B1&CHAR(10)),FIND(CHAR(13),B1&CHAR(13))))

or:-

=LEFT(B1,FIND(CHAR(10),SUBSTITUTE(B1,CHAR(13),CHAR(10))&CHAR(10)))

 

[pgc01]

Hi

Another option:

=LEFT(B1,MIN(FIND(CHAR({10,13}),B1&CHAR({10,13})))-1)

 

[Rick Rothstein]

And yet another option...

=LEFT(B1,FIND(CHAR(10),SUBSTITUTE(B1,CHAR(13),CHAR(10))&CHAR(10)))

 

[Ruddles]

=LEFT(B1,MIN(FIND(CHAR({10,13}),B1&CHAR({10,13})))-1)

Good catch - everyone else incuding me left the -1 out!