add extra space before comma

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

clarknv - 19 Mar 2007 14:21 GMT

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 to this Message

Toppers - 19 Mar 2007 14:29 GMT

If there is only ONE comma try Edit=> Replace=>Find What : ","(no quotes)
=>Replace with: " ," (again, no quotes) [blank followed by comma].

Test first!

> 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
[quoted text clipped - 8 lines]
> many times to get the department that creates the extra space report to just
> remove the extra space with no luck)

Reply to this Message

clarknv - 19 Mar 2007 14:50 GMT

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.

> If there is only ONE comma try Edit=> Replace=>Find What : ","(no quotes)
> =>Replace with: " ," (again, no quotes) [blank followed by comma].
[quoted text clipped - 13 lines]
> > many times to get the department that creates the extra space report to just
> > remove the extra space with no luck)

Reply to this Message

Toppers - 19 Mar 2007 15:49 GMT

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

> That worked great!
> Maybe you can help me with one other problem on the same 2 reports.
[quoted text clipped - 19 lines]
> > > many times to get the department that creates the extra space report to just
> > > remove the extra space with no luck)

Reply to this Message

clarknv - 19 Mar 2007 16:24 GMT

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!

> Perhaps you can do a "wild card" lookup:
>
[quoted text clipped - 31 lines]
> > > > many times to get the department that creates the extra space report to just
> > > > remove the extra space with no luck)

Reply to this Message

Toppers - 19 Mar 2007 16:45 GMT

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.

> The problem is that the format is this: LastName , FirstName
> It could show up like this in the other report: LastName Suffix , FirstName
[quoted text clipped - 39 lines]
> > > > > many times to get the department that creates the extra space report to just
> > > > > remove the extra space with no luck)

Reply to this Message

clarknv - 19 Mar 2007 16:52 GMT

Thankx so much for your help today. I will use this site often.

> For the following combinations:
>
[quoted text clipped - 53 lines]
> > > > > > many times to get the department that creates the extra space report to just
> > > > > > remove the extra space with no luck)

Reply to this Message