names to new column

[Tollkiwi]

Hello

I have a 6000 record 2003 excel worksheet where on column contains the Service Name.
The values in here are of varying length, alpha/numeric. Samples Below
Matthews, Hayley
S May BDEV (note this has a space at the front)
151105 Srivinasa Pulla
Adam Benli

The desired result
a new column which has the values without spaces at the front, without the numbers at the front, and those with a comma have been reversed and the comma removed. example below
Hayley Matthews
S May BDEV
Srivinasa Pulla


Any help is appreciated.

Regards
Kerry

[Sisyphus]

Hi,
Here is the code that does the job. You should paste it into a normal code module.
Open the VB Editor window, either by pressing Alt+F11 or by selecting Tools / Macro / Visual Basic Editor from your CommandBar.
You should see the Project Explorer window on the left of the VBE window. It lists the open workbooks’ work and code sheets. If you don’t see it, open it by selecting View / Project Explorer or by pressing Ctrl+R.
To insert a code module right-click on the name of your VB Project (that is, the name of your workbook). In the dropdown that opens, select Insert / Module. It will be called Module1 by default if there aren’t any other modules with default names already.
Paste the code into this module.

Code: Select all

Option Explicit
Option Base 0

    Const SourceCol As String = "A"
    Const TargetCol As String = "D"
    Const FirstRow As Long = 2

Public Sub ConvertData()

    Dim Sc As Long
    Dim Tc As Long
    Dim R As Long
    Dim S As String
   
    Sc = Asc(SourceCol) - 64
    Tc = Asc(TargetCol) - 64
   
    For R = FirstRow To Cells(65536, 1).End(xlUp).Row
        S = Trim(Cells(R, Sc).Value)
        RemoveLeadingNumbers S
        If Len(S) Then ReverseNameOrder S
        Cells(R, Tc).Value = S
    Next R
End Sub

Private Sub RemoveLeadingNumbers(ByRef S As String)

    Do While IsNumeric(Left(S, 1))
        S = Trim(Mid(S, 2))
    Loop
End Sub

Private Sub ReverseNameOrder(ByRef S As String)
   
    Dim Sp() As String
   
    Sp = Split(S, ",")
    If UBound(Sp) > 0 Then
        S = Trim(Sp(1)) & " " & Trim(Sp(0))
    End If
End Sub


You can call the code from the Tools menu. Press Tools / Macro / Macros.
From the list that opens select “ConvertData” and press the Run button.
Be sure that you do this while the sheet containing the data to be changed is visible to you, i.e. it is the ActiveSheet.

You may adjust the code's action by adjusting the constants at the top of the module: SourceCol, TargetCol and FirstRow. You must use capital letters to specify the columns.

The code itself is very simple:

Code: Select all

        S = Trim(Cells(R, Sc).Value)                   ' Read the original cell value and remove leading / trailing blanks
        RemoveLeadingNumbers S                     ' Remove leading numbers
        If Len(S) Then ReverseNameOrder S       ' Reverse the name order and remove any comma
        Cells(R, Tc).Value = S                            ' Write the changed string to the Target cell


The 30 names on my demonstration sheet take about a quarter second. If that guess is nearly accurate, your 6000 names should all be changed within less than 3 minutes.

[Tollkiwi]

Hi Sisyphus

Thank you for the code it worked perfectly.

Took less than 30secs to create a new column and process 5227 records

Thank you

Regards
Kerry