Transfer to adjacent columns
 

Hi

In column A I have a long list that was originally in 4 columns and without
separators for the old columns, ie Basingstoke & Deane £644 £1,140 +77%
The last 3 are all in the same format but the names vary in size.

Is it possible to write a macro to transfer the % to col 4, the 2nd £ amount
to col 3, the 1st £ amount to col 2 and leave the names in col A?

--
TIA
Tim

Transfer to adjacent columns
 

Assumes xl2000 or later

Sub AdjustData()
Dim cell as Range, v as Variant, ipos as long
for each cell in Range(Cells(1,1),Cells(1,1).End(xldown))
v = Split(Application.Trim(cell)," ")
cell.offset(0,1) = v(ubound(v)-2)
cell.offset(0,2) = v(ubound(v)-1)
cell.offset(0,3) = v(ubound(v))
ipos = instr(1,cell.value,"£",vbTextCompare)
cell.value = Trim(Left(cell.value,ipos-1))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy


"Tim" wrote in message
...
Hi

In column A I have a long list that was originally in 4 columns and
without
separators for the old columns, ie Basingstoke & Deane £644 £1,140 +77%
The last 3 are all in the same format but the names vary in size.

Is it possible to write a macro to transfer the % to col 4, the 2nd £
amount
to col 3, the 1st £ amount to col 2 and leave the names in col A?

--
TIA
Tim

Transfer to adjacent columns
 

Thanks Tom - it worked fine
--
Tim


"Tom Ogilvy" wrote:

Assumes xl2000 or later

Sub AdjustData()
Dim cell as Range, v as Variant, ipos as long
for each cell in Range(Cells(1,1),Cells(1,1).End(xldown))
v = Split(Application.Trim(cell)," ")
cell.offset(0,1) = v(ubound(v)-2)
cell.offset(0,2) = v(ubound(v)-1)
cell.offset(0,3) = v(ubound(v))
ipos = instr(1,cell.value,"£",vbTextCompare)
cell.value = Trim(Left(cell.value,ipos-1))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy


"Tim" wrote in message
...
Hi

In column A I have a long list that was originally in 4 columns and
without
separators for the old columns, ie Basingstoke & Deane £644 £1,140 +77%
The last 3 are all in the same format but the names vary in size.

Is it possible to write a macro to transfer the % to col 4, the 2nd £
amount
to col 3, the 1st £ amount to col 2 and leave the names in col A?

--
TIA
Tim