We help IT Professionals succeed at work.

Separating a field into two or more fields

rmccor asked
Is there a way to do the opposite of concatenation? I'm working with a database that has a FullName field and want to separate it into First, Middle and Last name fields. Our company is about to buy a new software package and the company we're buying from needs to convert all of our current databases, so I want to clean them up as much as possible to reduce our cost.  Thanks.  
Watch Question

You can use several methods to break up a single field into multiple fields.  The easiest, but not always the correct way is to use the Right(Field, x) where x is the amount of digits you want starting from the right.
For example,
Right("SampleText",4) would result in "Samp"

The same goes for Left(Text,x) but with names, you can not use either of these.

There is a command (I forget it's syntax) that will search for a specific character, and you can break apart a field by that -- just like a comma delimated file.

Typically, you could do a search for the first occurance of a blank space.  The unfortunately thing is that there are some cases where there will be some with middle initials and others without, so in order to get a 100% clean data extract, it may involve a bit of manual manipulation.
The instr() function returns the position of a specific character in a string.  You can use it to find the "space" and then manipulate the FULLNAME based on that.  

You can set up a query to return the position of the space as (say) pos.  Like this

pos=   instr([fullname], " ")

Then, firstname would be:


Lastname would be:


OR THIS WAY USING instr() directly..

This should give you the first name.  It selects everything "up to" the first space.

Left([FullName],InStr([Fullname]," ")-1)

This gives everything AFTER the first space so it should usually give you the last name.

right([Fullname,len([fullname])-len(Left([FullName],InStr([Fullname]," "))

Try just putting these in a query and see what the results are.  If it is working OK, change the query to an update.

hi rmccor,

RoLaAus is correct about the right and left functions.
The other function, <There is a command (I forget it's syntax) that will...> , is the InStr function.


fullname =  "John William Doe" (in a textbox called "txtFName")
the first blank (space) is at the 5 position.

Dim intPos As Integer
    intPos = InStr(Me.txtFName, " ")
    MsgBox intPos

The messagebox will show 5.

ofcourse u will need to use the function in code to loop through a recordset, or query.
unfortunatly there is no easy solution for this kind of problem, since some people will have a name combined of First- and lastname, and some people will have more than 1 middle name. (so 4)
which means there will need to be some manually adjustments required.


You can pretty easily deal with middle names if you do some manual, hands on work with the query design grid. You need to "iterate" through the table, parsing the names until there are no "spaces" left to be returned by the instr() function. Here's one way to do it:

First of all get all the firstnames into the table, this is easy.  Add a new field for FIRSTNAME, do an update of firstname... update to:

left([Fullname],(instr([fullname]," ")-1))

Now, let's work on the rest:

Design a query to get all the fullnames from the table,
Add these columns to the query:

Position1:   instr()[Fullname[," ")
Leftover1:   right(len([fullname])-[position1])
Position2:   instr([leftover1]," ")
Leftover2:   right(len([leftover1])-[position2])
Position3:   instr([leftover2]," ")
Leftover3:   right(len([leftover2])-[position3])

By the time you get to leftover3, you should pretty well have a last name isolated in one of those columns.  Use the postion number or other means to selectively update your lastname field with the correct name.


Or, for a quick and dirty (and decidedly "low-tech") solution, export the field out to a text file and open it using MS Excel using whatever delimiter you have separating the names. (I presume space) Then you will have two or more columns that you can import back into Access into as many fields.
If you have more than 65535 records, this won't work, of course. In that case, you might consider using a function recursively to search for the space character (or whatever delimiter) and storing the separated names in an array till you write them out to separate fields. Should be straightforward ;)

Good Luck




     It still seems like I would run in to problems when some people had middle names while some did not. Their last names would fall under different columns. The reason I was hoping my question would have an easy solution is because in Microsoft Outlook, when you enter a name directly into the name field, the program automatically splits it correctly into first, middle and last name fields. They probably use the same instr() function you mention and then code to see how many spaces appear in a name to decide if there is a middle name or not. It's not worth all the effort for me right now, but I would be interested to know exactly how they do it.  I already knew about being able to use Excel to split the columns (Excel has a Text to Columns command, but you still run into problems with some people having middle names). I think I'll just do it manually right now, but in the future, I'll try out the instr() function with some code and see if I can get it to work for me. Thanks.

I haven't looked at how Outlook deals with this kind of input, however I would expect it, too, would have trouble correctly parsing a name like Michael Adam Johnson St. James, III.

The tips provided here assumed you would want to correctly split the names ONCE, and then in the future insure that they were entered in the appropriate fields.  As such, having to run a query once or twice and making some assesments of the results would not be too big a deal.

If you are looking for something that insures that the names are correctly entered every day, I know a technique that gives you about a 30% success ratio:

an explicitly- worded, precisely-defined, all-situations-covered, example-filled, instruction memo to all employees.  :)

I have been having similar issues.  But mine is abit different.  My name field contains email addresses, names formatted so that the last nam is first separated by a comma and the first name and middlie intial are separated by a space.  Lastly, there are fields-within the same report mind you, that appear in the nominal order separated by spaces.

After reading your answers above, it looks like I am going to have to do a Case setup.  The only issue with that will be getting rid of everything after the '@' symbol.

Any ideas???

Explore More ContentExplore courses, solutions, and other research materials related to this topic.