Need Macro To Save Myself Hours of Formatting

TeachMeToExcel

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top