Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
I have 2 reports that i use in my department. 1 report lists names but it
has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
If there is only ONE comma try Edit= Replace=Find What : ","(no quotes)
=Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
That worked great!
Maybe you can help me with one other problem on the same 2 reports. I have also noticed a problem on my vlookup where one report might be missing Jr or has an extra last name if they were married etc. What can I do to get around the fact that they are missing Jr/Sr or maiden-married name. "Toppers" wrote: If there is only ONE comma try Edit= Replace=Find What : ","(no quotes) =Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
Perhaps you can do a "wild card" lookup:
=VLOOKUP("*Doe*", C:D,2,0) OR =VLOOKUP("*" & A1 & "*",C:D,2,0) where A1="Doe" HTH "clarknv" wrote: That worked great! Maybe you can help me with one other problem on the same 2 reports. I have also noticed a problem on my vlookup where one report might be missing Jr or has an extra last name if they were married etc. What can I do to get around the fact that they are missing Jr/Sr or maiden-married name. "Toppers" wrote: If there is only ONE comma try Edit= Replace=Find What : ","(no quotes) =Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
The problem is that the format is this: LastName , FirstName
It could show up like this in the other report: LastName Suffix , FirstName -or- LastName MarriedName , FirstName -or- Lastname , First Name Initial, or any combination. So the wildcard needs find a match even though the extra characters could be in the beginning, middle or end of the string. (Impossible! Right???) If our departments would just talk to one another and get a standard format for names, I would not be greying so early! "Toppers" wrote: Perhaps you can do a "wild card" lookup: =VLOOKUP("*Doe*", C:D,2,0) OR =VLOOKUP("*" & A1 & "*",C:D,2,0) where A1="Doe" HTH "clarknv" wrote: That worked great! Maybe you can help me with one other problem on the same 2 reports. I have also noticed a problem on my vlookup where one report might be missing Jr or has an extra last name if they were married etc. What can I do to get around the fact that they are missing Jr/Sr or maiden-married name. "Toppers" wrote: If there is only ONE comma try Edit= Replace=Find What : ","(no quotes) =Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
For the following combinations:
Doe, John Jr Jr Doe, John Doe James, Mary Doe Jr Martin, Alfie With C1="Doe" matches are found BUT there is a problem if there is more than one "Doe". =VLOOKUP("*" & C1 &"*",A1:B4,2,0) As you say, the real unswer is a company standard format. "clarknv" wrote: The problem is that the format is this: LastName , FirstName It could show up like this in the other report: LastName Suffix , FirstName -or- LastName MarriedName , FirstName -or- Lastname , First Name Initial, or any combination. So the wildcard needs find a match even though the extra characters could be in the beginning, middle or end of the string. (Impossible! Right???) If our departments would just talk to one another and get a standard format for names, I would not be greying so early! "Toppers" wrote: Perhaps you can do a "wild card" lookup: =VLOOKUP("*Doe*", C:D,2,0) OR =VLOOKUP("*" & A1 & "*",C:D,2,0) where A1="Doe" HTH "clarknv" wrote: That worked great! Maybe you can help me with one other problem on the same 2 reports. I have also noticed a problem on my vlookup where one report might be missing Jr or has an extra last name if they were married etc. What can I do to get around the fact that they are missing Jr/Sr or maiden-married name. "Toppers" wrote: If there is only ONE comma try Edit= Replace=Find What : ","(no quotes) =Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add extra space before comma
Thankx so much for your help today. I will use this site often.
"Toppers" wrote: For the following combinations: Doe, John Jr Jr Doe, John Doe James, Mary Doe Jr Martin, Alfie With C1="Doe" matches are found BUT there is a problem if there is more than one "Doe". =VLOOKUP("*" & C1 &"*",A1:B4,2,0) As you say, the real unswer is a company standard format. "clarknv" wrote: The problem is that the format is this: LastName , FirstName It could show up like this in the other report: LastName Suffix , FirstName -or- LastName MarriedName , FirstName -or- Lastname , First Name Initial, or any combination. So the wildcard needs find a match even though the extra characters could be in the beginning, middle or end of the string. (Impossible! Right???) If our departments would just talk to one another and get a standard format for names, I would not be greying so early! "Toppers" wrote: Perhaps you can do a "wild card" lookup: =VLOOKUP("*Doe*", C:D,2,0) OR =VLOOKUP("*" & A1 & "*",C:D,2,0) where A1="Doe" HTH "clarknv" wrote: That worked great! Maybe you can help me with one other problem on the same 2 reports. I have also noticed a problem on my vlookup where one report might be missing Jr or has an extra last name if they were married etc. What can I do to get around the fact that they are missing Jr/Sr or maiden-married name. "Toppers" wrote: If there is only ONE comma try Edit= Replace=Find What : ","(no quotes) =Replace with: " ," (again, no quotes) [blank followed by comma]. Test first! "clarknv" wrote: I have 2 reports that i use in my department. 1 report lists names but it has an extra space after the last name: Doe , John The other report does not have the extra space: Doe, John. The report with the extra spaces has been used for a long time and many of our daily functions depend on this information. Is there a way to add an extra space after the last name in the second report so that the names match the main report? I am currently using a vlookup to extract numbers from each report but the names don't match. I tried using true instead of false in the vlookup but it pulls the name closest to it which is the wrong information (I have tried many times to get the department that creates the extra space report to just remove the extra space with no luck) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete extra space | Excel Worksheet Functions | |||
Extra comma at the end of a function | Excel Discussion (Misc queries) | |||
Extra Line Space | Excel Discussion (Misc queries) | |||
Extra Line Space | Excel Discussion (Misc queries) | |||
Extra line space | Excel Discussion (Misc queries) |