Cleaning Data without dropping leading zeros

rodgeraj

New Member
Joined
Mar 10, 2006
Messages
10
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
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
 
Does your data have to stay formatted as "number" or can you format it as "text"? If you don't need to do anything by use the numbers as a reference, it may be easiest to format them as Text, then the leading zeros should stay.
 
Upvote 0
rodgeraj,

Sample data before the macro:


Excel Workbook
ABCDE
1CUST_NO PART_NO OTHER_DATA
200003495 0993940322 XXXX
300043959 099401003495 XXXX
400003943 00893942399012 XXXX
5
Sheet1



After the macro:


Excel Workbook
ABCDE
1CUST_NO PART_NO OTHER_DATACUST_NOPART_NOOTHER_DATA
200003495 0993940322 XXXX000034950993940322XXXX
300043959 099401003495 XXXX00043959099401003495XXXX
400003943 00893942399012 XXXX0000394300893942399012XXXX
5
Sheet1




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Code:
Option Explicit
Sub SplitData()
Dim c As Range, Sp
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c.Offset(, 2).Resize(, 2).NumberFormat = "@"
  Sp = Split(c.Value, "  ")
  c.Offset(, 2) = Sp(0)
  c.Offset(, 3) = Sp(1)
  c.Offset(, 4) = Sp(2)
Next c
Columns("C:E").Columns.AutoFit
End Sub


Then run the "SplitData" macro.
 
Upvote 0

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