PDA

View Full Version : simple (i think) text manipulation



indieman
03-16-2007, 07:48 AM
Hi,
Im am creating an excel application to automate a manual task that is taking 10 minutes to do, but i have run into a problem. The last step i need is to save the file in a certain format.
I need to know how to split up a text string and re-arrange it. For example i need

Andrew Smith --> Smith, Andrew
Robert Samuel Read --> Read, Robert Samuel
Sarah Chaggar-Brown --> Chaggar-Brown, Sarah

Can anybody help? The amount of times this manual process happens means if i can automate it I'll save us 50 man hours a week, it will look good on my one-to-one.

Thanks,
Ben

mdmackillop
03-16-2007, 09:06 AM
Hi Ben,
Welcome to VBAX
Here's some code and an example. You'll need to manipulate it for your SaveAs name. If you need assistance, let us know.
BTW, I never like commas in file names. Better to avoid punctuation.

Option Explicit
Sub SortNames()
Dim Cel As Range, Surname As String
For Each Cel In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Surname = Split(Cel, " ")(UBound(Split(Cel, " ")))
Cel.Offset(, 1) = Surname & ", " & Trim(Left(Cel, Len(Cel) - Len(Surname)))
Next
End Sub

indieman
03-16-2007, 09:19 AM
thanks for this! Ill give it a go.

Yeh i dont like commas but its company policy unfortunately.


Edit:
This works a charm! much appreciated.