| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting First Name
Hello,
I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
=MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1)
Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
I am getting "#VALUE!" error.
"CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Try this for the first word
=IF(ISERR(FIND(" ",A2)),A2,IF(RIGHT(LEFT(A2,FIND(" ",A2)-1))=",",LEFT(A2,FIND(" ",A2)-2),LEFT(A2,FIND(" ",A2)-1))) Last word =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024)) This Add-in make it easy to insert the formulas in a new column http://www.rondebruin.nl/datarefiner.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Ruan" wrote in message ... Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
The formula will return #VALUE! if cell A1 is empty, or if it contains a
number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Sorry, I should have been more specific. Not all the names have a Middle
Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1,FIND(" ",A1)-1)))) Thanks Ruan |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Try
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Perfect. Thank you so much Bob.
"Bob Phillips" wrote in message ... Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Hi Bob.........
Your formula overcame the OP's objections to my original one, but will return strange results with different combinations of longer and shorter names, and with cells containing lastname, firstname middlename For example: Washington, Bill returns Bill (as it should) Washington, Bill P. returns Bill P. Washington, Bill Percival returns Bill Perciv Bond, Benjamin returns Benja This one appears to work better..... =IF(A1="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Hi Chuck,
As you know, extracting names can be fraught with problems <vbg. For instance, just try your new solution with St. John, Ian I just supplied a solution to the problem as defined. Personally, I have an addin that uses regular expressions to split names, but again this works for names like Bob Phillips, Bill Percival, Ian St John, Baron von Richtofen, etc, but was not designed for Phillips, Bob, etc. Nightmare isn't it <vbg Bob "CLR" wrote in message ... Hi Bob......... Your formula overcame the OP's objections to my original one, but will return strange results with different combinations of longer and shorter names, and with cells containing lastname, firstname middlename For example: Washington, Bill returns Bill (as it should) Washington, Bill P. returns Bill P. Washington, Bill Percival returns Bill Perciv Bond, Benjamin returns Benja This one appears to work better..... =IF(A1="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Hi all
My formula is working for Phillips, Bob also but have other problems <g -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Hi Chuck, As you know, extracting names can be fraught with problems <vbg. For instance, just try your new solution with St. John, Ian I just supplied a solution to the problem as defined. Personally, I have an addin that uses regular expressions to split names, but again this works for names like Bob Phillips, Bill Percival, Ian St John, Baron von Richtofen, etc, but was not designed for Phillips, Bob, etc. Nightmare isn't it <vbg Bob "CLR" wrote in message ... Hi Bob......... Your formula overcame the OP's objections to my original one, but will return strange results with different combinations of longer and shorter names, and with cells containing lastname, firstname middlename For example: Washington, Bill returns Bill (as it should) Washington, Bill P. returns Bill P. Washington, Bill Percival returns Bill Perciv Bond, Benjamin returns Benja This one appears to work better..... =IF(A1="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Hi Bob.........
Yeah, I hear 'ya.... LOL.......they seem to be a moving target........... Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Hi Chuck, As you know, extracting names can be fraught with problems <vbg. For instance, just try your new solution with St. John, Ian I just supplied a solution to the problem as defined. Personally, I have an addin that uses regular expressions to split names, but again this works for names like Bob Phillips, Bill Percival, Ian St John, Baron von Richtofen, etc, but was not designed for Phillips, Bob, etc. Nightmare isn't it <vbg Bob "CLR" wrote in message ... Hi Bob......... Your formula overcame the OP's objections to my original one, but will return strange results with different combinations of longer and shorter names, and with cells containing lastname, firstname middlename For example: Washington, Bill returns Bill (as it should) Washington, Bill P. returns Bill P. Washington, Bill Percival returns Bill Perciv Bond, Benjamin returns Benja This one appears to work better..... =IF(A1="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Extracting First Name
Such as with de Bruin, Ron <vbg
Bob "Ron de Bruin" wrote in message ... Hi all My formula is working for Phillips, Bob also but have other problems <g -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Hi Chuck, As you know, extracting names can be fraught with problems <vbg. For instance, just try your new solution with St. John, Ian I just supplied a solution to the problem as defined. Personally, I have an addin that uses regular expressions to split names, but again this works for names like Bob Phillips, Bill Percival, Ian St John, Baron von Richtofen, etc, but was not designed for Phillips, Bob, etc. Nightmare isn't it <vbg Bob "CLR" wrote in message ... Hi Bob......... Your formula overcame the OP's objections to my original one, but will return strange results with different combinations of longer and shorter names, and with cells containing lastname, firstname middlename For example: Washington, Bill returns Bill (as it should) Washington, Bill P. returns Bill P. Washington, Bill Percival returns Bill Perciv Bond, Benjamin returns Benja This one appears to work better..... =IF(A1="","",IF(COUNTIF(A1,"*,*")0,MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1),A1)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Try =MID(A1,FIND(" ",A1)+1,FIND(" ",A1&" ",FIND(" ",A1)-FIND(" ",A1)+1)-1) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ruan" wrote in message ... Sorry, I should have been more specific. Not all the names have a Middle Initial. So, when I don't have a middle initial (Bond, James), your formula displays an error. Otherwise, it works perfectly for the names with a Middle Initial. Ruan "CLR" wrote in message ... The formula will return #VALUE! if cell A1 is empty, or if it contains a number. I understood it was to contain something like "Bond, James P.", and you wanted only the "James" part extracted. It's a fairly long formula, perhaps email word-wrap messed it up......it all goes in one cell. Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: I am getting "#VALUE!" error. "CLR" wrote in message ... =MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1) Vaya con Dios, Chuck, CABGx3 "Ruan" wrote: Hello, I was able to extract the Last Name from a string, however I am having difficulties extracting the First Name, especially when there is a Middle Initial involved. Example: Bond, James P. I just need to extract "James". Here is my formula I used for extracting the Last Name - =UPPER(IF(ISNUMBER(FIND(",",A1)),TRIM(LEFT(A1,FIND (",",A1)-1)),TRIM(LEFT(A1, FIND(" ",A1)-1)))) Thanks Ruan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extracting numbers from alphanumeric cells | Excel Worksheet Functions | |||
| Extracting information from records to another sheet automatically | Excel Worksheet Functions | |||
| Extracting data from Pivot | Excel Discussion (Misc queries) | |||
| Extracting info from word and displaying in an excel spreadsheet | Excel Discussion (Misc queries) | |||
| Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Linear Mode
