Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
AUTOFILTER-2 non-adjacent columns | New Users to Excel | |||
How to keep data in two adjacent columns together | New Users to Excel | |||
combining adjacent columns | Excel Discussion (Misc queries) | |||
Printing non-adjacent columns | Excel Discussion (Misc queries) |