Hopefully I can word what I am looking for in a way that makes sense to everyone. I have a customer report that I run and it exports in an ugly way.
It looks like this:
Address Suburb State Postcode Delivery Instructions Phone Number Fax Number Email Address
All the above information is in column A, and I would like to create a macro that has the information in column A-F so each heading has a new column.
I can record really basic macros, so I will probably need someone to write one for me so I can simply copy and paste *sorry*.
Hopefully this is really simple to someone and they are willing to help me out. Any suggestions/advice is greatly appreciated!
To splits lines is a cell and move each line to a new cell on the same row.
1. Data start at ROW 2
2. The data is in column A
3. Column next to A is where the data needs to be populated
4. Each line in the data cells should be moved to a new cell in the same row
1. Read the assumptions
2. Make a backup of the file
3. Press ALT + F11 and insert a new module
4. Paste the code (after steps)
5. Execute the code
Sub SpiltData() Dim lMaxRows As Long Dim lRowBeanCounter As Long Dim vPos As Variant Dim sHold As String Dim sTemp As String Dim iCellCounter As Integer Dim lStartAtRow As Long lStartAtRow = 1 lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row For lRowBeanCounter = lStartAtRow To lMaxRows sTemp = Cells(lRowBeanCounter, "A") iCellCounter = 2 Do While sTemp <> "" vPos = 0 vPos = InStr(1, sTemp, Chr(10)) If vPos > 0 Then sHold = Left(sTemp, vPos - 1) sTemp = Trim(Mid(sTemp, vPos + 1)) Else sHold = sTemp sTemp = "" End If iCellCounter = iCellCounter + 1 Cells(lRowBeanCounter, iCellCounter) = sHold Loop Next lRowBeanCounter End Sub
Thanks to rizvisa1 for this tip on the forum.
- Excel - Macro to format report
- Excel macro to format report ✓ - Forum - Excel
- Excel change date format to dd/mm/yyyy - How-To - Excel
- Excel macro auto increment number - How-To - Excel
- Excel macro create new workbook and copy data ✓ - Forum - Excel
- Excel macro compare two sheets and highlight differences ✓ - Forum - Excel