Need Macro To Save Myself Hours of Formatting

[TeachMeToExcel]

Hello All. I greatly Appreciate your help. I receive daily emails with large amounts of data I need to format through a macro. I copy the data into notepad and then excel, and the data appears as such, where the | represents a new column:

35M |1/1/2029| 3.22 |5| Eaton| Vance| -| TABS|
40M |1/1/2030| 3.3| 5| Eaton| Vance| -| TABS|
40M |1/1/2031| 3.36| 5| Eaton| Vance| -| TABS|
18,100M |1/1/2035| 3.82| 4| NUVEEN| ADVISORY| CORP |
1,000M |1/1/2036| 98| 3.75| Asset| Preservation|
Advisors| Inc.|
150M |1/1/2036| 98| 3.75| Bess| Investments|
250M |1/1/2036| 98| 3.75| Calcutta| Investment|
Management|
3,000M |1/1/2036| 98| 3.75| CL| King| &| Associates|
1,000M |1/1/2036| 98| 3.75| Core| Performance|
1,000M 1/1/2036| 98| 3.75| Dinosaur| Securities|
500M 1/1/2036| 98| 3.75| Dockside| Asset|
Management|
200M |1/1/2036| 98| 3.75| E| Trade| Securities |
500M |1/1/2036| 98| 3.75| George| K.| Baum| &|
Company|
2,200M |1/1/2036| 98| 3.75| Goldman| Sachs| PWM |
4,000M |1/1/2036| 98| 3.75| Hopwood| Lane| Trading|
625M |1/1/2036| 98| 3.75| INWOOD| CAPITAL|
MANAGEMENT,| LLC|
250M | 1/1/2036| 98| 3.75| KF| Advisors|

I need to format the data above so that: 1. The first column is just the number without the M. 2. The last column (5th) is the entire name of the company. You'll notice some names spill over into the next line, so I need the recombine those to names that precede them. The format should look as follows:


35 |1/1/2029| 3.22| 5 |Eaton Vance - TABS|
40 |1/1/2030| 3.3| 5 |Eaton Vance - TABS|
40 |1/1/2031| 3.36| 5 |Eaton Vance - TABS|
18,100 |1/1/2035| 3.82 |4 |NUVEEN ADVISORY CORP |
1,000 |1/1/2036| |98 |3.75| Asset Preservation Advisors Inc.|
150 |1/1/2036| |98 |3.75 |Bess Investments|
250 |1/1/2036| |98 |3.75 |Calcutta Investment Management |
3,000 |1/1/2036| |98 |3.75 |CL King & Associates|
1,000 |1/1/2036| |98 |3.75 |Core Performance |
1,000 |1/1/2036| |98 |3.75 |Dinosaur Securities|
500 |1/1/2036| |98 |3.75 |Dockside Asset Management |
200 | 1/1/2036| |98 |3.75| E Trade Securities|
500 |1/1/2036| |98 |3.75 |George K. Baum & Company|
2,200 |1/1/2036| |98 |3.75 |Goldman Sachs PWM |
4,000 |1/1/2036| |98 |3.75 |Hopwood Lane Trading |
625 |1/1/2036| |98 |3.75 |INWOOD CAPITAL MANAGEMENT, LLC |
250 |1/1/2036| |98 |3.75| KF Advisors|

Thanks Guys!!! So much time will be saved.

 

[TeachMeToExcel]

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.

 

[jscranton]

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]

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]

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]

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]

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]

Try this:

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