Split Excel Cell String 2

[Russie]

Hi.

I have a column in Excel which contains names in the following format:

BLOGGS, Joe

I would like to be able to split this list of names into two columns i.e.

A B
BLOGGS Joe

Is this possible with the use of some code?

Thanks.

Russie

 

[lupins46]

Yes, but why bother.
Data>TextToColumns will do it without code.

 

[Crowley16]

lol, nice one lupins...

I've been using code for ages, but I've got like a standard module which is imported everywhere so doesn't take much time to do but still, very useful to know...

have a star...

p.s. if you do want the code, then:

Private tmpSurN                        'temporary Surname storing
Private tmpForN                        'temporary Forename storing

Private idx As Integer                 'integer counter

'function to parse fullname into surname and forename
Function ParseName(sh As String, col As String, colPre As String, start As Integer)
   'sh is the worksheet to be worked on
   'col is the column in sh that the names are stored
   'col pre is the column in sh that stores the forename
   
   idx = start     'records might not start at 1st row

   'loops through all non empty cells
   With Worksheets(sh)
      Do While idx < Worksheets(sh).Range(col & Rows.Count).End(xlUp).Offset(1, 0).row
         'finds the first " " delimiter and separates name based on that
         If (InStr(.Range(col & CStr(idx)).Value, " ") <> 0) Then
            tmpForN = LTrim(RTrim(Left(.Range(col & CStr(idx)).Value, InStr(.Range(col & CStr(idx)).Value, " "))))
            tmpSurN = LTrim(RTrim(Right(.Range(col & CStr(idx)).Value, Len(.Range(col & CStr(idx)).Value) - InStr(.Range(col & CStr(idx)).Value, " "))))
            
            'then puts the forename into colPre
            Worksheets(sh).Range(colPre & CStr(idx)).Value = tmpForN
            Worksheets(sh).Range(col & CStr(idx)).Value = tmpSurN
         End If
         
         idx = idx + 1
      Loop
   End With

End Function

Procrastinate Now!

 

[Crowley16]

p.s. look through it first before you try to use it, I've not used this for a while now so am not sure if everything works correctly or not...

Procrastinate Now!

 

[Russie]

Excellent Lupins.

Thankyou Crowley.