VBA remove leading zeros

[Lelewiwi]

Hi there!

I have a bunch of code that is working beautifully. I need to add something to the end that will remove leading zeros from negative numbers in column G. The current code shows negative numbers as "0-0.85". Any help is appreciated! Below is my current code:

Sub Macro1()

Dim strFile As String
Dim ws As Worksheet

Set ws = ActiveSheet

strFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If strFile = "False" Then Exit Sub

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = False
    .Refresh
End With

'
' Macro1 Macro
' cleans up UPS text data

' dim nRows as Long

' nRows = InputBox("Please find how many rows of data there are in the UPS Data file.", "UPS Data Rows", "Enter the number of rows HERE")

' create new cols for data
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Data"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Acct#"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Mo"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Amt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Amt1"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    
' extract data to new cols
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],18,6)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-3],14,4)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=DATE(""20""&RIGHT(RC[-1],2),LEFT(RC[-1],2),""01"")"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-5],5)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(LEFT(RC[-1],3),""."",RIGHT(RC[-1],2))"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
    
' populate data
    Range("B2:H2").Select
    Selection.AutoFill Destination:=Range("B2:H75000")
    Range("B2:H75000").Select
    
' paste acct#'s to new wksheet "First Split"
    Columns("C:C").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "First Split"
    Sheets("First Split").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
' clean up acct#'s + populate Amounts
    ActiveSheet.Range("$A$1:$A$75000").RemoveDuplicates Columns:=1, Header:=xlYes
    Range("B1").Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(Sheet1!R1C3:R48611C8,'First Split'!RC[-1],Sheet1!R1C8:R48611C8)" ' <-- need to match :R##### to # of rows w/ data
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B38") ' keep eye on #rows
    Range("B2:B38").Select
    ActiveWindow.SmallScroll Down:=-3
    
' paste acct#'s & months to new wksheet "Month Splits" + clean up data
    Range("B1").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Month Splits"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("A2").Select
    Sheets("Sheet1").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B2").Select
    Sheets("Sheet1").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    
' populate data
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(Sheet1!R2C8:R75000C8,Sheet1!R2C3:R75000C3,'Month Splits'!RC[-2],Sheet1!R2C5:R75000C5,'Month Splits'!RC[-1])" ' <-- need to match R##### to # of rows w/ data
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C360") ' keep eye on #rows
    Range("C2:C360").Select

End Sub

 

[RobP]

Presumably its the LEFT hand side of this CONCAT thats causing the trouble ? what does the actual data look like (individually) before this in the cells it grabs them from ?

Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(LEFT(RC[-1],3),""."",RIGHT(RC[-1],2))"

 

[Joe4]

In cases like this, so much depends on the actual data. So it would be helpful to see the following:
- examples of what your data currently looks like
- your expected output from those examples
- what your current code is doing to those examples

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Lelewiwi]

@RobP This is what the data looks like before the initial splits happen:

12159999912221122129403210190063123456789DOE, JOHN 0000000000000000000000000000000000000000000000000000000000000000000P019820920202110250000000000082970-1000008500-085
11599999901231222129283203950104123456788DOE, JANE A0000000000000000000000000000000000000000000000000000000000000000000P019921231202201310000000000108090-0500004000-020
115999999012312221293022101903961234567899DOE, JOE 0000000000000000000000000000000000000000000000000000000000000000000P019811104200104100000000000080620-10000100000100
The number of rows varies every month. It seems to hit a snag when there is a negative number as shown below:

The positive numbers with leading zeros transfer over to column H just fine, but the negative numbers do not. I'm thinking it is being told to extract the last 5 digits of each line and for negative numbers that will include a leading zero before the negative (-). I did not write the code but am trying to make it work so we don't have to manually transfer the negative numbers to column H.

 

[Joe4]

Perhaps it should only be extracting the final 4 numbers, not 5?
Can you confirm that?
Are there any cases in which it would need to pull back 5 numbers?
If so, and you have a value like:

7-085

what exactly does that mean/translate to in real numbers?

 

[Lelewiwi]

Yes, it needs to pull 5 digits but no more than that. I just need it to drop the leading zero when the number is negative.

 

[Joe4]

But what if the leading number in front of the negative is NOT a zero?
Then what should happen?

 

[Lelewiwi]

It is always a zero. If it is not, we have bigger problems...

 

[Joe4]

OK, then replace this part of your code:

    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"

with this:

    Range("H2").FormulaR1C1 = "=SUBSTITUTE(RC[-1],""0-"",""-"")+0"

Note that most anytime in VBA you have one cell ending in "Select", and the next begins with "ActiveCell" or "Selection", those two lines can be combined together to one.
(My guess is the original code is the result of recording a Macro which is very literal!)
It is not necessary to select ranges before updating them. Combining those cases will not only shorten your code, but speed it up too, as selecting objects slows your code down.

 

[Lelewiwi]

Thank you, that worked except that I now have a bug later in the code in this portion

' paste acct#'s to new wksheet "First Split"
    Columns("C:C").Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "First Split"
    Sheets("First Split").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
   
' clean up acct#'s + populate Amounts
    ActiveSheet.Range("$A$1:$A$75000").RemoveDuplicates Columns:=1, Header:=xlYes
    Range("B1").Select
    Range("B1").FormulaR1C1 = "Amount"
    Range("B2").FormulaR1C1 = "=SUMIF(Sheet1!R1C3:R48611C8,'First Split'!RC[-1],Sheet1!R1C8:R48611C8)" ' <-- need to match :R##### to # of rows w/ data
    Range("B2").AutoFill Destination:=Range("B2:B38") ' keep eye on #rows
    Range("B2:B38").Select
    ActiveWindow.SmallScroll Down:=-3
   
' paste acct#'s & months to new wksheet "Month Splits" + clean up data
    Range("B1").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Month Splits"
    Range("A1").FormulaR1C1 = "Account"
    Range("B1").FormulaR1C1 = "Month"
    Range("C1").FormulaR1C1 = "Amount"
    Range("A2").Select
    Sheets("Sheet1").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B2").Select
    Sheets("Sheet1").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Month Splits").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
   
' populate data
    Range("C2").FormulaR1C1 = "=SUMIFS(Sheet1!R2C8:R75000C8,Sheet1!R2C3:R75000C3,'Month Splits'!RC[-2],Sheet1!R2C5:R75000C5,'Month Splits'!RC[-1])" ' <-- need to match R##### to # of rows w/ data
    Range("C2").AutoFill Destination:=Range("C2:C360") ' keep eye on #rows
    Range("C2:C360").Select
   
End Sub