Need Macro to create formulas and split "comma delimited" cells into rows.

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
Two questions below...

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....
For Step 2... with the number of comma delimited items in Column D always be the same as the number of delimited items in Column F? If not, how should the output look?


Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
So when we are done, the values in Column F from Step 2, namely this...

M0002456 (04P)
M0002457 (05P)
M1230477 (02A)

will become this...

M0002456
M0002457
M1230477

Is that correct?
 

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29
Two questions below...


For Step 2... with the number of comma delimited items in Column D always be the same as the number of delimited items in Column F? If not, how should the output look?


So when we are done, the values in Column F from Step 2, namely this...

M0002456 (04P)
M0002457 (05P)
M1230477 (02A)

will become this...

M0002456
M0002457
M1230477

Is that correct?

Yes, ALWAYS same number of comma delimited items in both columns

Yes, this is correct
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
Yes, ALWAYS same number of comma delimited items in both columns

Yes, this is correct
Good, but I forgot to ask you one other question. For Column A... are the numbers after the two or three letters always composed of four digits like you showed or could the number of digits vary?

Also, did you want the rearranged data to overwrite the original data or did you want it placed on another worksheet?
 

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29

ADVERTISEMENT

Good, but I forgot to ask you one other question. For Column A... are the numbers after the two or three letters always composed of four digits like you showed or could the number of digits vary?

Also, did you want the rearranged data to overwrite the original data or did you want it placed on another worksheet?

Column A, could be different number of letters and numbers. but will always want a space between last letter and first number

Overwrite would be best, but separate worksheet is also fine, whichever is easier.
Basically, I have a program exporting to csv which is not very user friendly and will always output this way, so need the macro to make things easier.

Have to go pickup wife from work, so will be away from computer for a while.
Will respond to any further questions ASAP.

Thanks for your help Rick
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
Give this macro a try...
Code:
Sub RearrangeData()
  Dim R As Long, C As Long, X As Long, LastRow As Long, LastColumn As Long, RowCount As Long
  Dim Index As Long, Data As Variant, RowsOut As Variant, ItemsD() As String, ItemsF() As String
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
  Data = Range("A2").Resize(LastRow - 1, LastColumn)
  Index = 2
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "#" Then
        Data(R, 1) = Left(Data(R, 1), X - 1) & " " & Mid(Data(R, 1), X)
        Exit For
      End If
    Next
    ItemsD = Split(Replace(Data(R, 4), ", ", ","), ",")
    ItemsF = Split(Replace(Data(R, 6), ", ", ","), ",")
    RowCount = UBound(ItemsD) + 1
    ReDim RowsOut(1 To RowCount, 1 To LastColumn)
    For X = 1 To RowCount
      For C = 1 To LastColumn
        RowsOut(X, C) = Data(R, C)
      Next
      RowsOut(X, 4) = ItemsD(X - 1)
      RowsOut(X, 6) = ItemsF(X - 1)
    Next
    Cells(Index, "A").Resize(RowCount, LastColumn) = RowsOut
    Index = Index + RowCount
  Next
  Columns("F").Replace " (*)", "", xlPart
End Sub
 

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29
Give this macro a try...
Code:
Sub RearrangeData()
  Dim R As Long, C As Long, X As Long, LastRow As Long, LastColumn As Long, RowCount As Long
  Dim Index As Long, Data As Variant, RowsOut As Variant, ItemsD() As String, ItemsF() As String
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
  Data = Range("A2").Resize(LastRow - 1, LastColumn)
  Index = 2
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "#" Then
        Data(R, 1) = Left(Data(R, 1), X - 1) & " " & Mid(Data(R, 1), X)
        Exit For
      End If
    Next
    ItemsD = Split(Replace(Data(R, 4), ", ", ","), ",")
    ItemsF = Split(Replace(Data(R, 6), ", ", ","), ",")
    RowCount = UBound(ItemsD) + 1
    ReDim RowsOut(1 To RowCount, 1 To LastColumn)
    For X = 1 To RowCount
      For C = 1 To LastColumn
        RowsOut(X, C) = Data(R, C)
      Next
      RowsOut(X, 4) = ItemsD(X - 1)
      RowsOut(X, 6) = ItemsF(X - 1)
    Next
    Cells(Index, "A").Resize(RowCount, LastColumn) = RowsOut
    Index = Index + RowCount
  Next
  Columns("F").Replace " (*)", "", xlPart
End Sub

Thanks Rick!
The part "split comma fields to rows" works perfectly.
But unfortunately, it is not putting a space between letters and numbers in column B

Also, could you please comment the code so I can try to follow it? I would like to learn enough so I don't have to keep asking questions and can start answering some :)
And, how to change the columns, so instead of D and F I can make it B and O or T and BF etc...?

Tia,
Al
 

Estarriol

New Member
Joined
Jul 26, 2005
Messages
29
Thanks Rick!
The part "split comma fields to rows" works perfectly.
But unfortunately, it is not putting a space between letters and numbers in column B

Also, could you please comment the code so I can try to follow it? I would like to learn enough so I don't have to keep asking questions and can start answering some :)
And, how to change the columns, so instead of D and F I can make it B and O or T and BF etc...?

Tia,
Al


Sorry, Also can you please add as the last piece after all of the above:
"Find current path and filename" (ALWAYS .csv)
Save this file as "current path and filename" & "-Fixed"
eg: c:\temp\abc.csv becomes c:\temp\abc.fixed.csv also, path may have spaces, eg: "c:\temp\testing of script\abc.csv" becomes "c:\temp\testing of script\abc-fixed.csv"


Many thanks for your help Rick.
Al
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top