How to fix spaces between last name comma first name

[Dave]

I need to remove and fix a various number of spaces that are between last
name, first name without removing spaces between first name and middle
initial. Can you help?

Examples:
Smith, John A = Smith, John A
Smith,John A = Smith, John A
Smith, John A = Smtih, John A

 

[KARL DEWEY]

Backup database.
Replace(Replace([YourNameField], ",",", "), " "," ")

Replace([YourNameField], ",",", ") -- adds space after comma to correct as
in your second example.

Replace(Replace([YourNameField], ",",", "), " "," ") -- exchanges two
spaces for one. It will need to be run several times for where there are
multiple spaces as in your first and third example.

 

[Jerry Whittle]

Is this a one time thing or something that you'll need to do often?

If one time, go to the table and click on the field in question. Go to Edit,
Replace and put in two spaces in the Find What. Then put in one space in the
Replace With. Run this multiple times until the count of replacements hits
zero.

You could also start out with something like 5 spaces then subtract a space
each time.

Of course you can't see the spaces but you can tell where they are in the
Find What box.

You might want to make a backup copy of the table or entire database file
just in case things go bad.

 

[Dave]

This is something that happens routinely. I knew about the replace, but I
didn't know if there was some type of wild card that could be used for an
undertimed number of spaces.

 

[Michel Walsh]

Replace(Replace(Replace(originalString ," ", " *"), "* ", ""), "*", "")


Note that I assumed * is a character which do not occur in the original
string.

Be very careful about the spaces near the *. In the innermost replace, a
space precedes the *. In the middlemost replace, a space follows the *, in
the outermost, no space is associated to the *. The second argument of the
innermost replace, the one which follows originalString, is made of TWO
spaces.



Vanderghast, Access MVP

 

[Dave]

Interesting. I already ran the multiple replacements for todays data, but I
will definitely try this tomorrow.

Why would this not replace the space between the First name and middle
initial?

 

[Michel Walsh]

Because it never operates on isolated space (occurring just once). Our
algorithm deals with multiple consecutive spaces, though, removing all but
the first one.

Assume neither * neither % initially occur in the string. The idea is to
first rename two consecutive spaces by something else, say instead of
space-space, we now have percent-star. If more than two spaces where
initially occurring, say three consecutive spaces, we then deal with what is
now star-percent (which we remove) and finally, remove any star left,
definitively a character we introduces (since we assume there was no star,
initially, in the string). Sure, you may see that the percent symbol is not
strictly required, and so, I just used space instead of it (but the
algorithm could have been more evident if I would have kept the percent
character).



Vanderghast, Access MVP

 

[Dave]

Thank you, this did the trick

Because it never operates on isolated space (occurring just once). Our
algorithm deals with multiple consecutive spaces, though, removing all but
the first one.

Assume neither * neither % initially occur in the string. The idea is to
first rename two consecutive spaces by something else, say instead of
space-space, we now have percent-star. If more than two spaces where
initially occurring, say three consecutive spaces, we then deal with what is
now star-percent (which we remove) and finally, remove any star left,
definitively a character we introduces (since we assume there was no star,
initially, in the string). Sure, you may see that the percent symbol is not
strictly required, and so, I just used space instead of it (but the
algorithm could have been more evident if I would have kept the percent
character).



Vanderghast, Access MVP

 

[Guest]

Here is anopther method usingf SQL:

Update TableName
SET NameField = left(NameField, Instr(NameField,",")) & " " &
Trim(Right(NameField, len(NameField)-Instr(NameField,",")))

Brian