Solved

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

Posted on 2008-06-18
4
547 Views
Last Modified: 2008-06-18
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.
0
Comment
Question by:MLG0001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 125 total points
ID: 21815465
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
0
 

Author Comment

by:MLG0001
ID: 21817022
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?
0
 

Author Comment

by:MLG0001
ID: 21817201
Figured the answer out - Set Global  = False and it only returns the first match of the pattern.
Thanks again for your assistance!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21817297
No worries - glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help Creating PowerShell Script 4 67
CSV file copy field 1 to field 2 2 63
VB script help 23 50
Classic ASP - Display returned sql output parameter 9 57
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question