Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) "
this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
macro needs to do something like following (but in VBS, not my mangled English)
.
Step 1, insert a column next to A, check rows down and for however many rows, make above formula (edited by you geniuses to include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567"
.
Step 2
column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields.
D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A)
.
need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields)
.
eg: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
.
then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
.
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
.
Hope my mangled English is clear enough. Anything unclear, please ask.
.
.
Many thanks in advance for your help.
Al
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) "
this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
macro needs to do something like following (but in VBS, not my mangled English)
.
Step 1, insert a column next to A, check rows down and for however many rows, make above formula (edited by you geniuses to include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567"
.
Step 2
column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields.
D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A)
.
need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields)
.
eg: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
.
then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
.
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
.
Hope my mangled English is clear enough. Anything unclear, please ask.
.
.
Many thanks in advance for your help.
Al