Need Macro To Save Myself Hours of Formatting

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
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.
 

robertdseals

Active Member
Joined
May 14, 2008
Messages
291
The first part is pretty easy. In your macro, just do a find/replace "M" with "". Then I think you can concatenate all the columns 5 through whatever.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
Try this. This does not delete the columns for the broken parts of your company names but it does insert a new column and concatenate all the peices.

Code:
Sub fixData()
Dim i, LR, LC
Dim wb As Workbook
Dim colV As String
Dim fullName As String


Set wb = ThisWorkbook


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


'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
 

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
I follow you, but I don't know macro language well enough for the concatenate portion. I understand the logic needs to be if the cell in column A is a word and not a number that it needs to concatenated to the previous row column 5 (as well as 6, 7, etc. depending on the length of the name). I just don't know how to write it.
 

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
14
Unfortunately that macro didn't work. I'm not proficient enough to figure out where to tweak. This macro added a column E that concatenated the company name to the right with no spaces and it didn't add on the rest of the name if it continued in the second row, it just deleted that portion.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
The tweak is that you have some sort of hard return in your raw data which is the data from one row to wrap to another. I don't have time at the moment to diagnose it.
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
What are you doing to the data when it comes out of the email? Why, for example, are some line wrapping and some lines not?
 

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
I tested your string above and you have vbLf and vbCrLF characters that are causing the wrapping. if you remove those prior to pasting your data into excel and delimiting the problem should be solved.
 

Forum statistics

Threads
1,084,856
Messages
5,380,318
Members
401,664
Latest member
traveler84

Some videos you may like

This Week's Hot Topics

Top