How do I batch cut and paste across columns in Access 2007

place this function in a regular module

then write a query like this

select [name], splitname(1,[name]) as firstName,splitname(2,[name]) as LastName,splitname(1,[name]) as MI
from tableX

Function SplitName(ByVal idx As Integer, ByVal sName As String) As String

    Dim infArr() As String
   
    SplitName = ""
    If sName <> "" Then
        infArr = Split(sName, " ")
        Select Case idx
            Case 1
                SplitName = infArr(0)
            Case 2
                If UBound(infArr) > 0 Then SplitName = infArr(1)
            Case 3
                If UBound(infArr) > 1 Then SplitName = infArr(2)
            Case 4
                If UBound(infArr) > 2 Then SplitName = infArr(3)
            Case 5
                If UBound(infArr) > 2 Then SplitName = infArr(4)

        End Select
    End If
End Function

Select allOpen in new window

I don't know that there's an easy way just within Access to do it, but it could be done with a vb-script. if you've already got a connection to the database w/ your ASP page and the added columns exist...

pull recordset
  FOR each record
    set recordID  < key variable from record
    set namevariable  < from original field
    separateName = split(namevariable, " ")
    sql update recordID  lastname = separateName(0) firstname = separateName(1) initial = separateName(2)

could do some additional fiddling with the text to peel off the commas and periods as well. save that asp page (i'm assuming from the zones listed you're working within a web application) and run it once to see what you get. obviously run against a backup copy of your database first.

You could export the table into excel and then open the excel file, then in three columns create formulas.

I have attached a sample spreadsheet with the formulas.

This will work so long as the full name column has the data in the format:

Surname, Forname Initial


sample.xls

That article includes a sample MDB file.

Also keep in mind that multi-word name parts are always, always going to give you fits :)

Thanks everyone!

mitzilla,

you did not even try the first post ???

Capricorn1,
Actually I  tried all of the posts. But my understanding of access is still really really at a beginner status.
I couldn't get the module to be seen by the query.  I had to poke around for like fifteen minutes before I even found that feature. Apologies, I'm sure yours would of worked perfectly and I did give it a shot. I just couldn't get my head around it.
Thanks

<I couldn't get the module to be seen by the query.  >

you could have posted a question, so the other will know if you are having difficulty with the posted suggestion and can post information to help you with the problem.