Formula that does text to rows

[cmorten82]

I have attached what the data looks like and what I want the outcome to be. I want to dump the data in excel as is and have it make the changes. The fields in columns E-J will have a specific data set such as a dollar amount or an invoice number, etc., but as you can see the data pulls in two amounts in cell G4 separated by a dash. This is because there should technically be two lines of data instead of one but when I export it pulls it in this way. So A-D I want the data to get copied down based on how many amounts there are and E-J will apply one of the pieces of data to each line. If there is two amounts then there is two lines if there is three then three lines and so on. You may also notice after the last item in each of the cells in E-J a single letter exists this letter means nothing and will be removed. The reason this is this way is because the web form I download out of has a matrix where I add GL, Amount, Invoice#, Tracking#, and FY and if I have to add another invoice with seperate amounts or a different GL I click on a button that allows me add a second row in the web form and then I plug in the numbers. Once I am done I click download and it takes the data in that form and essentially creates a Journal entry or atleast that is what I would like to happen. Down the road I will be pulling downloads like the one I attached but they will be much more data intensive like 20-100 rows of data and not 2 rows

[p24leclerc]

I don't know of a formula to do this but it can be done with a macro. Are you open to macros?

[cmorten82]

I haven't had much experience with macros but im open to give it a shot it's always a good time to learn something new.

[cmorten82]

so here is what I found so far

Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, " - ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub

This works great for an individual cell that has separation by a Hyphen(-) but I need to apply it to any cells in columns E-J not just one cell, and I also need to make sure that it doesn't
overwrite the rows below it. I think my goal is to be able to dump a data set in to a spreadsheet where there is data in the range A1:J50 and then run the macro by and have it do the conversion throughout
the entire data set A-J. On a side note the data in columns A-D would not be seperated they would just be copied down to the next cells. I have reattached a sample data set.

[p24leclerc]

Here is a macro that should work for you.
It is not a sophisticated one. More like brute force but it works.
And it is easier for you to understand the process.
See attached workbook for a working example.

Please Login or Register  to view this content.

[cmorten82]

Thank you this worked perfectly.