Split Name

[juneau730]

Hello all,

I have a macro in Excel that splits a column that contains a full name (Smith,Bob H) into three new columns (LN, FN, MN) and it works great there. I am trying to get something similar that will perform the same function from Access, prior to importing the data into a database table, but I need to to either be embedded into the "import" sub code or as a call to a secondary sub.

I did search for this and saw a lot for excel, but that's not what I need

 

[alansidman]

MS Access Query Tip: How to Split Names to Extract First & Last Names

Here's the scenario. You have data in a table that contains full names, and you want to be able to split the field so that you have first and last names in separate fields. Unlike Excel, Acc

probiztechnology.com

If necessary, you can then run an update query if you need to update a table.

 

[dellehurley]

Hello all,

I have a macro in Excel that splits a column that contains a full name (Smith,Bob H) into three new columns (LN, FN, MN) and it works great there. I am trying to get something similar that will perform the same function from Access, prior to importing the data into a database table, but I need to to either be embedded into the "import" sub code or as a call to a secondary sub.

I did search for this and saw a lot for excel, but that's not what I need

Hi
You could use something like this

Sub SplitName()
Dim FullName As String
Dim LN As String, FN As String, MN As String, OtN As String
FullName = "Smith,Bob H"
    LN = Split(FullName, ",")(0)
    OtN = Split(FullName, ",")(1)
    FN = Split(OtN, " ")(0)
    If Len(OtN) <> Len(Replace(OtN, " ", "")) Then
        MN = Split(OtN, " ")(1)
    End If
End Sub

The reason I used the comma to the Surname and Other names is in case someone has a Surname with a gap in it eg Van der Walt.
D

 

[juneau730]

MS Access Query Tip: How to Split Names to Extract First & Last Names

Here's the scenario. You have data in a table that contains full names, and you want to be able to split the field so that you have first and last names in separate fields. Unlike Excel, Acc

probiztechnology.com

If necessary, you can then run an update query if you need to update a table.

Thank you Ron, I will take a look at this.
I do need to update a table in the Access dBase, and I have that portion of the coding work already written, tested and working. I just need to incorporate the name split portion of it now.

 

[juneau730]

Hi
You could use something like this

Sub SplitName()
Dim FullName As String
Dim LN As String, FN As String, MN As String, OtN As String
FullName = "Smith,Bob H"
    LN = Split(FullName, ",")(0)
    OtN = Split(FullName, ",")(1)
    FN = Split(OtN, " ")(0)
    If Len(OtN) <> Len(Replace(OtN, " ", "")) Then
        MN = Split(OtN, " ")(1)
    End If
End Sub

The reason I used the comma to the Surname and Other names is in case someone has a Surname with a gap in it eg Van der Walt.
D

Thank you dellehurley.

May be a silly question, but how does this code know where to read the full name from and where to put the LN, FN and MI?

 

[dellehurley]

Thank you dellehurley.

May be a silly question, but how does this code know where to read the full name from and where to put the LN, FN and MI?

There are several ways but here is one presuming you wish to loop through a list of values in a range, this is one. Feel free to ask if you have any questions.
D

Sub SplitName()
Dim FullName As String, LastRow As Long, MnCnt As Integer
Dim LN As String, FN As String, MN As String, OtN As String, M As String
Dim Cell As Range, Rng As Range

    With Ws 'change to your worksheet reference
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A2:A" & LastRow)  'update to the column your FullNames are in
        For Each Cell In Rng
            FullName = Cell.Value
            LN = Split(FullName, ",")(0)
            OtN = Split(FullName, ",")(1)
            If Left(OtN, 1) = " " Then   'removes the space if there is one after comma
                OtN = Right(OtN, (Len(OtN) - 1))
            End If
            FN = Split(OtN, " ")(0)
            MnCnt = Len(OtN) - Len(Replace(OtN, " ", ""))
                Do Until MnCnt = 0              ' this loop is in case a erson has more than one Middle Name
                    M = Split(OtN, " ")(MnCnt)  'eg. Fullname=Smith,Bob H J Mn= H J or Smith, Bob Henry James then Mn =Henry and James
                    If MN = "" Then
                        MN = M
                    Else: MN = MN & " " & M
                    End If
                    MnCnt = MnCnt - 1
                Loop
            Cell.Offset(0, 1).Value = LN 'update to the column your Last Names are in
            Cell.Offset(0, 2).Value = FN 'update to the column your First Names are in
            Cell.Offset(0, 3).Value = MN 'update to the column your Middle Names are in
        Next
    End With
End Sub

 

[alansidman]

@dellehurley

Your VBA solution appears to be for Excel. The OP has requested (if I understand clearly) for an MS Access solution.

 

[welshgasman]

Why not just run your Excel macro from access before you import?

 

[Micron]

If you have no control over the way the names are in the db, then that is one thing.

If you do, then your mistake was to store the first and last names in one column, and in that case, the proper fix would be to split the data into columns. Normally a db field should contain only one value which represents an attribute of an entity. If your Excel columns were so designed, it would be easier to get that into Access.

I am trying to get something similar that will perform the same function from Access

Consider linking to the Excel sheet that has had this data split already, then copy it to a proper Access table by way of an append query. If the db table fields are correctly designed, you may also eliminate data type issues that can arise when Excel number data comes over to Access as text when importing. You probably should also create one or more indexes in the db table so that you don't repeat records every time you run the update query.

I'm on a cruise ship this week, so forgive me if I don't stay on top of this thread.

 

[dellehurley]

@dellehurley

Your VBA solution appears to be for Excel. The OP has requested (if I understand clearly) for an MS Access solution.

OMG I'm currently working in Access and Excel and had a mind fart on day 2 and forgot that this was on Access' board. Thanks for pointing out the error.