Is it possible move part of my data from one column to another?

[j4c06]

For instance, if I get a table with a column full of names that all have a title like Mr. or Ms. or a combination of title, first name, and last name all in one field. Is it possible to split that data and move it to individual fields?

 

[John Big Booty]

Welcome to the forum.

Is the data consistent, in it's structure? For example; Title (terminated with a full stop), First Name (terminated with a comma) the Last name. If it is it should be reasonably a reasonably straight forward process to normalise the data. If not it will be significantly harder.

 

[j4c06]

Unfortunately not they all came in looking like this. (Mr. John Doe) Only spaces separating each word. Any ideas?

 

[John Big Booty]

OK so long as that format is consistent, the following should do the trick;

    Me.Title = Left(Me.TextData, InStr(Me.TextData, " "))
    
    Me.Fname = Mid(Me.TextData, Len(Me.Title) + 1, InStr(Len(Me.Title) + 1, Me.TextData, " ") - Len(Me.Title))
    
    Me.Sname = Right(Me.TextData, Len(Me.TextData) - (Len(Me.Title) + Len(Me.Fname)))

Check this link for an explanation of the various String manipulation functions.

 

[vbaInet]

Just a slightly different take on this:

Me.Title = Left(Me.TextData, InStr(Me.TextData, ".") - 1)
Me.Fname = Mid(Me.TextData, Len(Me.Title) + 3, InStr(Len(Me.Title) + 2, Me.TextData, " "))
Me.Sname = Mid(Me.TextData, Len(Me.Title & Me.FName) + 4)

Title = Mr
Fname = John
Sname = Doe

 

[j4c06]

Worked perfectly. Thanks for your help guys.