Home |
Search |
Today's Posts |
#1
|
|||
|
|||
=RIGHT (function)
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
Thank you all!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |