Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
delete extra space WBTKbeezy Excel Worksheet Functions 8 October 8th 06 01:49 AM
Extra comma at the end of a function SYerby Excel Discussion (Misc queries) 3 July 14th 06 04:53 PM
Extra Line Space Leesa Excel Discussion (Misc queries) 1 May 24th 05 09:37 PM
Extra Line Space Leesa Excel Discussion (Misc queries) 1 May 24th 05 09:19 PM
Extra line space JJ Excel Discussion (Misc queries) 1 February 19th 05 12:32 AM


All times are GMT +1. The time now is 06:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"