=RIGHT (function)

  • Thread starter Philippe L. Balmanno
  • Start date
P

Philippe L. Balmanno

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
 
J

JulieD

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.
 
B

Bernie Deitrick

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
 
P

Philippe L. Balmanno

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.
 
J

JulieD

Hi Philippe

sorry i thought you wanted to do this ... when you said about
is it all sorted now or do you need additional assistance.
 
C

CLR

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

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

Vaya con Dios,
Chuck, CABGx3
 
P

Philippe L. Balmanno

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.
 
P

Philippe L. Balmanno

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).
 
P

Philippe L. Balmanno

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).
 
B

Bernie Deitrick

Phillippe,

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

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

HTH,
Bernie
MS Excel MVP
 
W

wdk

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).
 
B

Bernie Deitrick

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
 
D

David McRitchie

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.
 
P

Philippe L. Balmanno

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


David McRitchie said:
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 said:
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
Click to expand...
Click to expand...
 
Top