• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

Remove Middle Initial from Name Value of String Before Importing value to Database Table

I am attempting to write code in a VBScript that will find and replace a pattern of  
space middle initial space
in a name value and replace it with a space only .
The value is coming from a text file that is being opened as an FSO and fields are defined through the use of the split function of each line in the file using Split(sLine, ",") The name field is one of the sections parsed in a line within this comma delimited text file.
I do not want to remove any additional names in the field. Some of the values in the name field have 2 last names that are not hypenated - I need to keep this the same.  Again, the pattern to be replaced should only be a single alpha character between two spaces.
For example, John S Doe to be John Doe.
John S Doe Smith will be changed to John Doe Smith.

I want the final format of the Name value to be updated before it gets inserted into a table.
  • 2
  • 2
1 Solution
A regex might be the way to go:

dim re
dim str1
dim str2

Set re = new regexp ' create the RegExp object
re.Pattern = "\b [A-Za-z] \b" ' set the pattern (word_boundary + " " + letter + " " + word_boundary)
re.Global = True

str1 = "John S Doe Smith" ' for testing only; your data will come from elsewhere
str2 = re.Replace(str1, "") ' modified string
MLG0001Author Commented:
Thanks this works GREAT ! I did make one minor change - instead of the replace having a "" I changed it to " " to make a space between the first and last name values.
I do have one more question regarding this - I have come across a few names (yes the data i received is horridly inconsistent) that contain 3 middle initials. This code will remove 2 of the 3 just as it is set up to do - but is there to still use similar code and have it only remove the first middle initial value it finds instead of 2 of the 3 ? An example John T I S Doe updates to be John S Doe as the code is set above...If I want it to be John I S Doe ...is that possible?
MLG0001Author Commented:
Figured the answer out - Set Global  = False and it only returns the first match of the pattern.
Thanks again for your assistance!
No worries - glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now