Question Splitting Names!

[stu_c]

hi all
currently we were running records in Excel with the customrers full name under one column "NAME",

i have imported the details into access now which we soon going to use, we have got over 3000 customers names this there any quick way to split the surname.

as you can see i have copied the data into both columns but can you do a find or replace or something of the sort to delete anything before the [SPACE]

 

[DreamGenius]

I would be inclined to do this slightly differently, if you're more comfortable in Access:

1. Rename Field 1 (First Name) as Full Name
2. Keep Field 2 (Surname) as it is
3. Add a 3rd Field, Forename

Then, using an Update Query, populate Surname and Forename as follows:

• Surname : Trim (Right ([Full Name], Len ([Full Name]) - InStr ([Full Name], " ") ) )
• Forename : Trim (Left ([Full Name], InStr ([Full Name], " ") ) )

This will get the vast majority. Once you're satisfied with the end result, drop Full Name from the table, leaving the other two as required.
In you're more comfortable in Excel, follow the same steps but replace InStr ([Full Name], " ") with Search (" ", RC) where RC is the cell on the same row which contains the full name. For example if Full Name is Column A and "K Simmonds" is on Row 2, that part of formula will be Search (" ", A2).

The advantage of using Excel is it's a little easier to use Autofilter to sense-check and amend your data before importing it into Access.

 

[Atomic Shrimp]

I would be inclined to do this slightly differently, if you're more comfortable in Access:

1. Rename Field 1 (First Name) as Full Name
2. Keep Field 2 (Surname) as it is
3. Add a 3rd Field, Forename/...

4. Discard the Full Name column as soon as you reasonably can - i.e. once you're sure you've captured it into the two separate fields and you're sure there aren't any queries, forms or reports etc that are relying on it.

If you want full name back at some point, you can still calculate it on the fly (Forename & " " & Surname). This is safer than keeping the full name field, because people's names do occasionally change, or need correcting if they've been entered wrong.

 

[neileg]

Watch out for names like Eric van Hee, Jill St. John, Mary Jane Brown etc.

 

[stu_c]

Hi
I am trying to do the spitting name thread you told me about a while back but cannot seem to do it i have got three columns now

Full name > Surname > Forename

I went into queries and typed and following into the criteria field under surname but i keep getting an error message saying...
"Data Type mismatch in criteria expression"

please help...

 

[DCrake]

Copy the following functions into a module

Public Function ParseLastName(AnyString As String) As String

Dim iPos As Integer
iPos = InStrRev(Anystring," ")  'Last space found in string position

If iPos = 0 Then
   ParseLastName = AnyString   'No spaces found
Else
   ParseLastName = Mid(AnyString, (iPos + 1)) 'Everything after last space
End If

End Function

Public Function ParseFirstNames(AnyString As String) As String


Dim iPos As Integer
iPos = InStrRev(Anystring," ")  'Last space found in string position

If iPos = 0 Then
   ParseFirstNames= AnyString   'No spaces found
Else
   ParseFirstNames= Left(AnyString, (iPos - 1)) 'Everything upto last space
End If

End Function

Then in your query use

fName:ParseFirstNames([FullName])
lName:ParseLastName([FullName])

David

 

[stu_c]

Copy the following functions into a module

Public Function ParseLastName(AnyString As String) As String
 
Dim iPos As Integer
iPos = InStrRev(Anystring," ")  'Last space found in string position
 
If iPos = 0 Then
   ParseLastName = AnyString   'No spaces found
Else
   ParseLastName = Mid(AnyString, (iPos + 1)) 'Everything after last space
End If
 
End Function

Public Function ParseFirstNames(AnyString As String) As String
 
 
Dim iPos As Integer
iPos = InStrRev(Anystring," ")  'Last space found in string position
 
If iPos = 0 Then
   ParseFirstNames= AnyString   'No spaces found
Else
   ParseFirstNames= Left(AnyString, (iPos - 1)) 'Everything upto last space
End If
 
End Function

Then in your query use

fName:ParseFirstNames([FullName])
lName:ParseLastName([FullName])

David

but where do i type the code for the query??

 

[stu_c]

thanks all

 

[Brianwarnock]

You have been given a couple of working solutions, assuming that there are only and always 2 names in the field, so what is the problem?

Brian

I see that you edited your post asking for somebody else to help whilst I was typing.