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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 2010
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
37,893
Office Version
  1. 2019
  2. 2010
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
37,893
Office Version
  1. 2019
  2. 2010
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,528
Messages
5,837,884
Members
430,520
Latest member
VenkateshRajaganesan

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