Extracting from a string

Hi Everyone

What i would like to achieve is to be able to do the following, original data is in column A1 data required is shown here in A2, i can get the data shown in A3 using
=LEFT(A2,FIND(" ",A2)+1) etc down the column. But ideally i want to be able to run a macro that would convert the data in column A1 to that in A2 but keep it in column A1 only and then loop down each row to convert each entry in column A1, is this possible do any of you kind folk have any suggestions

A1______________A2__________A3
Name
Warburton David__D Warburton__Warburton D
Jones Paul_______P Jones______Jones P
Hughes Mark_____M Hughes____Hughes M

Merry Christmas
David

Please Login or Register  to view this content.

Hi yudlugar

I think i understand the first line counts the number of rows of data the second line splits and concatenates, what does the (0) do on the end?

Many thanks
David

Please Login or Register  to view this content.

That takes the last cell in column A (A65536 for me in 2003), then .end(xlup) is similar to pressing ctrl+up to move back to the last cell with data in and .row returns the row number.

Therefore:

Please Login or Register  to view this content.

Is the reference for column A from row 2 to the last row with data in and

Please Login or Register  to view this content.

steps through each cell in that range one at a time, assigning the variable cl to the cell

Please Login or Register  to view this content.

will split the string cl (the value of the cell) into an array of elements, delimited by spaces. The array will start at the index 0 and move up to the total number of elements. So for a string like "LastName Firstname" it would return:
"LastName" as element 0
and
"FirstName as element 1

Please Login or Register  to view this content.

refers to the first element of the created array (LastName) and

Please Login or Register  to view this content.

refers to the second element of the created array (FirstName)

Finally:

Please Login or Register  to view this content.

The Left function takes the string in the first arguement and truncates it to the first n letters, where n is the second arguement. In our example it will therefore give the first letter of FirstName (F).
putting it all together you get:

Please Login or Register  to view this content.

which gives
F & " " & LastName
which is
F LastName
and this is assigned to cl.value, which overwrites the current value, and then moves onto the next cell.