I've got a macro that will clean my data, but it removes the leading zeros of numbers that are valuable to my line of business. Since I'm often working with several hundreds, if not thousdands, of rows I'd like to find a way to make this very efficient without compromising the data.
Example:
I run a report to pull customer and part numbers which can vary from 8 - 14 characters. This report comes from a mainframe system and exports to a text file. Any "space" between columns of the mainframe report is coverted in " " characters when I import the data into Excel.
CUST_NO PART_NO OTHER DATA
00003495 0993940322 XXXX
00043959 099401003495 XXXX
00003943 00893942399012 XXXX
To clean this data and make it presentable I had been doing "Find and Replace" repeatedly where I would find 2 spaces " " and replace it with one space " ". This wasn't as efficient as I like and it still removed leading 0s.
I also used a macro to clean the data
But this also removed the leading zeros, but worked faster than using Find and Replace.
Is there any way that I can accomplish my "clean-up" without losing the leading zeros on my data elements?
Thanks
Example:
I run a report to pull customer and part numbers which can vary from 8 - 14 characters. This report comes from a mainframe system and exports to a text file. Any "space" between columns of the mainframe report is coverted in " " characters when I import the data into Excel.
CUST_NO PART_NO OTHER DATA
00003495 0993940322 XXXX
00043959 099401003495 XXXX
00003943 00893942399012 XXXX
To clean this data and make it presentable I had been doing "Find and Replace" repeatedly where I would find 2 spaces " " and replace it with one space " ". This wasn't as efficient as I like and it still removed leading 0s.
I also used a macro to clean the data
Code:
Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Set Func = Application.WorksheetFunction
On Error Resume Next
Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub
For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next
But this also removed the leading zeros, but worked faster than using Find and Replace.
Is there any way that I can accomplish my "clean-up" without losing the leading zeros on my data elements?
Thanks