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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>