copy first line in a cell

N

Nev

I have an address
Any place
Anywhere
UK
in the above format. I want to be be to copy the first line into another
cell. I have found first word but some places have 2 names or even 3.

Can anyone help? I need to do this with a formula and not vba.

thanks

Nev
 
R

Ron Rosenfeld

I have an address
Any place
Anywhere
UK
in the above format. I want to be be to copy the first line into another
cell. I have found first word but some places have 2 names or even 3.

Can anyone help? I need to do this with a formula and not vba.

thanks

Nev
Click to expand...


=LEFT(A1,FIND(CHAR(10),A1)-1)

CHAR(10) is the LineFeed character; which should be at the end of the first
line.
--ron
 
N

Nev

That's great Thanks. If I wanted to get the 1st two line or second line
only, how would that work?

Thanks

Nev
 
R

Ron Rosenfeld

That's great Thanks. If I wanted to get the 1st two line or second line
only, how would that work?

Thanks

Nev
Click to expand...

Glad to help.

For the 1st two lines, you need to look for the second <lf> character. The
SUBSTITUTE worksheet function allows you to specify the instance of a
character.

So =SUBSTITUTE(A1,CHAR(10),CHAR(1),2) would substitute a CHAR(1) for the <lf>.
Then you just look for that:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR(1),2))-1)

To get just the second line, you use the MID function and go from the first
<lf> to your substituted CHAR(1).

So something like:

=MID(A1,FIND(CHAR(10),A1)+1,FIND(CHAR(1),
SUBSTITUTE(A1,CHAR(10),CHAR(1),2))-FIND(CHAR(10),A1)-1)

I find it simpler to use either Longre's free morefunc.xll add-in and the
regular expression formula:

=REGEX.MID(A1,".+",<LineNumber>)

for work like this.

The .xll will not work on strings longer than 255 characters, in which case I
would use a "home grown" VBA routine that does pretty much the same thing.

--ron
 
Top