E=mc2
asked on
How can I modify this script to be used in an Excel macro?
Script:
I would like to modify the script so that when it find numbers in column A, that it does not remove the leading 0's to those numbers but leaves them intact, and still goes on with the function set out for the macro.
For instance if it finds a line in column A which has the following:
00001 BULK DESCRIPTION 20 lbs
It will simply leave it as 1.
I want it to be:
00001
It seems to work fine if there are alpha characters, but if it's numbers, then it removes leading zeros and it should not.
Can we modify the script if possible?
Any assistance is appreciated.
Thank you.
Option Explicit
Sub Clean_Up()
Dim rng As Range
Dim rng2 As Range
Dim cl As Object
Dim lngRow As Long
Set rng = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row + 1)
On Error Resume Next
For lngRow = 1 To rng.Rows.Count
ActiveSheet.Cells(lngRow, 1).Value = Split(ActiveSheet.Cells(lngRow, 1).Value, " ")(0)
Next
On Error GoTo 0
Columns("T:Z").EntireColumn.Delete
Columns("B:R").EntireColumn.Delete
End Sub
I would like to modify the script so that when it find numbers in column A, that it does not remove the leading 0's to those numbers but leaves them intact, and still goes on with the function set out for the macro.
For instance if it finds a line in column A which has the following:
00001 BULK DESCRIPTION 20 lbs
It will simply leave it as 1.
I want it to be:
00001
It seems to work fine if there are alpha characters, but if it's numbers, then it removes leading zeros and it should not.
Can we modify the script if possible?
Any assistance is appreciated.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
That converts that cell to text, and so your 00001 remains 00001.