Problems splitting a text & number string using VBA

[Tamor]

I've done some basic VBA regarding excel spreadsheets in the past, but I'm having trouble with a recent problem. I need to split a string of text & numbers in a coloum into a few different cells.

I've done some extensive searching on google, and come across a few similar problems and solutions, but I am struggling to apply it to my situation.

I currently have a few large spreadsheets 7 coloums by a few hundred. Blank columns in B & E-I. Column D contains the text string I need to split. It looks like this

1234567:ABXY0000.Text with spaces - Fee.Surname.Forename.000000

I need to split the text string after AB, before the 'Text with spaces', delete the word ' - Fee', the Surname.Forename changing the "." to a ", " and split the last 000000, which is a reference number that can vary in length.

Column: Contents
D: 1234567:AB
E: XY0000
F: Text with spaces
G: Surname, Forename or Surname Forename
I: 000000

I need to remove all the full stops except the one between the forename & surname, though a space would surfice, delete a part of the text string and split it from 1 column to 5.

[Andy Pope]

Something like this maybe. Does D1 only. If correct it would need a loop to do all values in column D

Please Login or Register  to view this content.

[lecxe]

Hi

Another option. Please test for cell D1, if it works loop through the others.
If it doesn't work, please post some examples with real data:

Please Login or Register  to view this content.