I am looking to extract everything on the right of a " " (space) in a text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Hi

you might like to look at data / text to column
ensure you have a couple of blank columns to the right of the names
then select your list of names
choose
data / text to columns
delimited NEXT
untick tab tick space FINISH

and you should now have the first names in one column and the last names
(without a space) in the second column.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Phil,

For a list starting in cell A1, use this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Copy down to match your data list.

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Thanks, not what I expected it replaced the original column that had FN" "LN
with FN and the column on the Right with LN. I'll just create another
column to put the two back together again.

"JulieD" wrote in message
...
Hi

you might like to look at data / text to column
ensure you have a couple of blank columns to the right of the names
then select your list of names
choose
data / text to columns
delimited NEXT
untick tab tick space FINISH

and you should now have the first names in one column and the last names
(without a space) in the second column.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Hi Philippe

sorry i thought you wanted to do this ... when you said about
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

is it all sorted now or do you need additional assistance.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
Thanks, not what I expected it replaced the original column that had FN"
"LN with FN and the column on the Right with LN. I'll just create another
column to put the two back together again.

"JulieD" wrote in message
...
Hi

you might like to look at data / text to column
ensure you have a couple of blank columns to the right of the names
then select your list of names
choose
data / text to columns
delimited NEXT
untick tab tick space FINISH

and you should now have the first names in one column and the last names
(without a space) in the second column.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a
text line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

In B1 put this formula and copy down......

=MID(A1,FIND(" ",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"Philippe L. Balmanno" wrote:

I am looking to extract everything on the right of a " " (space) in a text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Got it basically I used a version of this method first replacin " " with ,
then with an out put in column A (FN) and ran it again and an output in
column B (LN) and then in column C I put every thing back together again
=B1&", "&A1.

"JulieD" wrote in message
...
Hi Philippe

sorry i thought you wanted to do this ... when you said about
column B can I compare column A to B and output the difference in
column
C stripping off the extra space after the FN?

is it all sorted now or do you need additional assistance.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
Thanks, not what I expected it replaced the original column that had FN"
"LN with FN and the column on the Right with LN. I'll just create
another column to put the two back together again.

"JulieD" wrote in message
...
Hi

you might like to look at data / text to column
ensure you have a couple of blank columns to the right of the names
then select your list of names
choose
data / text to columns
delimited NEXT
untick tab tick space FINISH

and you should now have the first names in one column and the last names
(without a space) in the second column.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a
text line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it
is in
column B can I compare column A to B and output the difference in
column
C stripping off the extra space after the FN?

TIA
Phil

Great, this is more along the lines that I was thinking of. I got the Last
name out of it perfectly. All I need to do is get the difference in column
A (WN - whole name) and column B (LN) to produce column C (FN).

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Phil,

For a list starting in cell A1, use this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Copy down to match your data list.

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a
text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Great, this is more along the lines that I was thinking of also. I got the
Last
name out of it perfectly. All I need to do is get the difference in column
A (WN - whole name) and column B (LN) to produce column C (FN).

"CLR" wrote in message
...
In B1 put this formula and copy down......

=MID(A1,FIND(" ",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"Philippe L. Balmanno" wrote:

I am looking to extract everything on the right of a " " (space) in a
text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it is
in
column B can I compare column A to B and output the difference in column
C stripping off the extra space after the FN?

TIA
Phil

Phillippe,

With the formula below in cell B1, simply use this in C1:

=TRIM(SUBSTITUTE(A1,B1,""))

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
Great, this is more along the lines that I was thinking of. I got the
Last
name out of it perfectly. All I need to do is get the difference in
column
A (WN - whole name) and column B (LN) to produce column C (FN).

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Phil,

For a list starting in cell A1, use this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Copy down to match your data list.

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space) in a
text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN and it
is
in
column B can I compare column A to B and output the difference in
column
C stripping off the extra space after the FN?

TIA
Phil

I've been trying to follow this and learning a lot. I'm wanting to do
something similar with a group of street addresses(123 E Main St.)
Although I've experimented with data text columns and concatenate, I
wanted to end up with 123 in one column and E Main St in a 2nd column
(will be used to sort by streetname then street number). I used the
suggest =right command to parse off the E Main St, but is there a
similar =left counterpart to just return the 123 portion (my street
numbers are various lengths in a column ranging from 2 digits to 5
digits).

Bernie Deitrick wrote:
Phillippe,

With the formula below in cell B1, simply use this in C1:

=TRIM(SUBSTITUTE(A1,B1,""))

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
Great, this is more along the lines that I was thinking of. I got
the
Last
name out of it perfectly. All I need to do is get the difference
in
column
A (WN - whole name) and column B (LN) to produce column C (FN).

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Phil,

For a list starting in cell A1, use this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Copy down to match your data list.

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space)
in a
text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN
and it
is
in
column B can I compare column A to B and output the difference
in
column
C stripping off the extra space after the FN?

TIA
Phil

wdk,

=LEFT(A1,FIND(" ",A1)-1)

And if you can have leading spaces (sometimes, data isn't always clean)

=LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)

HTH,
Bernie
MS Excel MVP


"wdk" wrote in message
ups.com...
I've been trying to follow this and learning a lot. I'm wanting to do
something similar with a group of street addresses(123 E Main St.)
Although I've experimented with data text columns and concatenate, I
wanted to end up with 123 in one column and E Main St in a 2nd column
(will be used to sort by streetname then street number). I used the
suggest =right command to parse off the E Main St, but is there a
similar =left counterpart to just return the 123 portion (my street
numbers are various lengths in a column ranging from 2 digits to 5
digits).

Bernie Deitrick wrote:
Phillippe,

With the formula below in cell B1, simply use this in C1:

=TRIM(SUBSTITUTE(A1,B1,""))

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
Great, this is more along the lines that I was thinking of. I got
the
Last
name out of it perfectly. All I need to do is get the difference
in
column
A (WN - whole name) and column B (LN) to produce column C (FN).

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Phil,

For a list starting in cell A1, use this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Copy down to match your data list.

HTH,
Bernie
MS Excel MVP


"Philippe L. Balmanno" wrote in message
...
I am looking to extract everything on the right of a " " (space)
in a
text
line.
I have a lit of names in the form of FN" "LN in column A.
In column B I want to extract the LN portion of column A.

I guess the next logical question would be if I extract the LN
and it
is
in
column B can I compare column A to B and output the difference
in
column
C stripping off the extra space after the FN?

TIA
Phil

This is what Bernie supplied you with before
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
for the right side.

The counterpart for the part to the left of the first space is
=LEFT(A1,FIND(" ",A1)-1)

Both formulas are dependent on there being a space in the cell
otherwise you will get a #VALUE! error.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wdk" wrote in message ups.com...
I've been trying to follow this and learning a lot. I'm wanting to do
something similar with a group of street addresses(123 E Main St.)
Although I've experimented with data text columns and concatenate, I
wanted to end up with 123 in one column and E Main St in a 2nd column

Bingo!!!! That addressed all of what I needed thank you very much.


"David McRitchie" wrote in message
...
This is what Bernie supplied you with before
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
for the right side.

The counterpart for the part to the left of the first space is
=LEFT(A1,FIND(" ",A1)-1)

Both formulas are dependent on there being a space in the cell
otherwise you will get a #VALUE! error.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"wdk" wrote in message
ups.com...
I've been trying to follow this and learning a lot. I'm wanting to do
something similar with a group of street addresses(123 E Main St.)
Although I've experimented with data text columns and concatenate, I
wanted to end up with 123 in one column and E Main St in a 2nd column

Thank you all!