copy first line in a cell

[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

 

[Don Guillett]

One way
=LEFT(D19,FIND(" ",D19,FIND(" ",D19)+1)-FIND(" ",D19))

 

[Don Guillett]

One way
=LEFT(D19,FIND(" ",D19,FIND(" ",D19)+1)-FIND(" ",D19))

 

[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

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

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

 

[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

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

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

 

[Nev]

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

Thanks

Nev

 

[Nev]

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

Thanks

Nev

 

[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

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

 

[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

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

 

[Nev]

Ron

That's brilliant - i will give it a go

Thanks

Nev

 

[Nev]

Ron

That's brilliant - i will give it a go

Thanks

Nev