Need Macro To Save Myself Hours of Formatting

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
I'm not doing anything to the data other than copying and pasting from outlook to notepad to excel. The data is delimited by spaces. The reason there is sometimes spill over of the company names onto the next line is purely a result of how the data is emailed to me. Some company names take two lines in the email and the formatting just copies into excel that way. I don't understand what you mean by vblF and vbCrLF characters causing the wrapping. Can you elaborate? I just need the data to go from the first example to the second example.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
Are the pipes ("|") not the delimiter? You basically have carriage returns in your raw data that needs to be removed before pasting into excel. Google search how to remove carriage returns.
 

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
The pipes represent a new column. When I'm copying and pasting, excel is delimiting by space. If it's not delimited by space the entire data set pastes in a single column and that won't work. Yes the raw data in the email has carriage returns, but there's no way to manipulate the data before I paste it in excel. And once it is in excel there are not carriage returns in the cells themselves, the second line of the company name which had carriage returns in the raw data just pastes into an entire new row below it. There is no issue with the copying and pasting and the characters. This is not the area where I can make changes. Once it is in excel, I need a macro that takes a line like this: (Where | is not part of the data, it represents a new column)

FIRST ROW: 1,000M |1/1/2036| 98| 3.75| Asset| Preservation|
SECOND ROW: Advisors| Inc.|
THIRD ROW: 150M |1/1/2036| 98| 3.75| Bess| Investments|

and makes it look like this:

FIRST ROW: 1,000 |1/1/2036| |98 |3.75| Asset Preservation Advisors Inc.|
SECOND ROW: 150 |1/1/2036| |98 |3.75 |Bess Investments|

Thanks again for the help.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
I understand that is what you want but achieving that result is going to be a challenge because you will need an additional step in a macro to recognize which rows are "second line of the company names".

Why can't you fix the carriage returns prior to pasting into Excel? I can write you a script that attempts to identify the wrapped rows but it could produce unexpected results unless you can identify the conditions that are ALWAYS present for a row that is the "second line of the company name."
 

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
Can you identify rows that begin with text vs numbers in your macro? Each row that begins with a word in cell A rather than a number value belongs to the company name in the previous row. And The answer would be because I don't know how. I get emailed this info in a certain format from an outside party that I have no control over. I obviously can't change it in outlook, so what do you suggest? I copy it into notepad, I could go through the notepad and manually fix every line with carriage returns, but there are thousands of these rows. Excel is the only method by which I could systematically change carriage returns. And once I paste into excel it takes that format above. So what option do I have between receiving this email and pasting into excel?
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
I can but note that if--for any reason--the name of a company starts with a number then it assume it is a proper row.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
Try this:

Code:
Sub fixData()
Dim i, LR, LC
Dim ws As Worksheet
Dim colV As String
Dim fullName As String


Set ws = ThisWorkbook.ActiveSheet


LR = Range("A" & Rows.Count).End(xlUp).Row


'rows that don't begin with a number to previous row


For i = 2 To LR


    If IsNumeric(Left(CStr(Cells(i, 1).Value), 1)) = False And Range("A" & i).Value <> "" Then


      With ws
        RowNumber = i - 1
        If .Cells(RowNumber, .Columns.Count) <> vbNullString Then
            Set LastCell = .Cells(RowNumber, .Columns.Count)
            pasteC = LastCell.Column
        Else
            Set LastCell = .Cells(RowNumber, .Columns.Count).End(xlToLeft)
            pasteC = LastCell.Column
        End If
      End With


    ws.Range("A" & i, "Z" & i).Cut Destination:=ws.Cells(i - 1, pasteC)
    
    ws.Rows(i).Delete
    
     i = i - 1
    
    End If
Next i




'fix non numeric values in column A


For i = 2 To LR
   colV = Cells(i, 1).Value
   colV = removeAlpha(colV)
   Cells(i, 1).Value = colV
   colV = ""
Next i


'insert row and concatenate company name
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  
'get last column


With ActiveSheet.UsedRange
        LC = .Columns(.Columns.Count).Column
End With


For i = 2 To LR


    For x = 6 To LC
    
        fullName = fullName & CStr(Cells(i, x)) & " "
        fullName = Trim(Replace(fullName, "  ", " "))
    Next x
         
    Cells(i, 5).Value = fullName
    fullName = ""
Next i


End Sub
Function removeAlpha(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[A-Za-z]"
    .Global = True
    removeAlpha = .Replace(r, "")
End With
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,090,220
Messages
5,413,146
Members
403,465
Latest member
Frankariye

This Week's Hot Topics

Top