Delete X characters from end of string

J

John D

Trying to "clean up" inherited database. In past data entry folk put
extraneous characters at the end of many people's Last Names, as in:

Smith, C.P.A.
Abelbaker PhD
Donneybrook, AICPA

etc.

Can't figure out how to delete a set number of characters from the right of
a string. I think I need to run a separate query for each specific set of
characters I want to delete (such as "C.P.A.", PhD, etc.). Coming after a
comma or a space in the last name isn't accurate (such as "Jones Jr", or a
two-word last name).

Help is appreciated - thanks.

John D
 
S

Smartin

John said:
Trying to "clean up" inherited database. In past data entry folk put
extraneous characters at the end of many people's Last Names, as in:

Smith, C.P.A.
Abelbaker PhD
Donneybrook, AICPA

etc.

Can't figure out how to delete a set number of characters from the right of
a string. I think I need to run a separate query for each specific set of
characters I want to delete (such as "C.P.A.", PhD, etc.). Coming after a
comma or a space in the last name isn't accurate (such as "Jones Jr", or a
two-word last name).

Help is appreciated - thanks.

John D
Click to expand...

Hi John D,

Your problem is not so much how to delete a set number of characters as
knowing where to break the name from the extras.

Perhaps you could write a VBA function and capitalize on the InStrRev()
function to truncate everything after the last space? This might leave
an undesired trailing comma, but you could attack that in a second routine.
 
F

fredg

Trying to "clean up" inherited database. In past data entry folk put
extraneous characters at the end of many people's Last Names, as in:

Smith, C.P.A.
Abelbaker PhD
Donneybrook, AICPA

etc.

Can't figure out how to delete a set number of characters from the right of
a string. I think I need to run a separate query for each specific set of
characters I want to delete (such as "C.P.A.", PhD, etc.). Coming after a
comma or a space in the last name isn't accurate (such as "Jones Jr", or a
two-word last name).

Help is appreciated - thanks.

John D
Click to expand...

As long as the number of different suffixes is manageable, you could
use:

Update YourTable Set YourTable.[FieldName] =
Replace(Replace(Replace(Replace([Fieldname],"C.P.A.",""),"PhD",""),"AICPA",""),",","")

Back up your data first.
 
J

John Nurick

How about a regular expression solution?

This expression

"(.*?)\s*\b(?:phD|AICPA|CPA|GD&R|ROTFL)\b.*$"

finds the first PhD or AICPA or whatever and returns the characters from
the beginning of the string up to but not including the white space
before it. The list of initials separated by pipe character can be
pretty much as long as you like. You can use it in a query with the
rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

e.g.

rgxExtract([LastName], "(.*?)\s*\b(?:phD|AICPA|CPA|GD&R|ROTFL)\b.*$" )

Assuming people don't have commas or periods in their names, you can get
rid of them at the same time by doing something like this:

rgxExtract(Replace(Replace([LastName]),",",""),".",""),
"(.*?)\s*\b(?:phD|AICPA|CPA|GD&R|ROTFL)\b.*$" )
 
Top