Cleaning Data without dropping leading zeros

[rodgeraj]

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

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

 

[sous2817]

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.

 

[rodgeraj]

Thanks sous - I don't know why that hadn't dawned on me.

Thanks!

 

[sous2817]

Glad it worked out. Best of luckw ith the rest of your project!

 

[hiker95]

rodgeraj,

Sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	CUST_NO PART_NO OTHER_DATA
2	00003495 0993940322 XXXX
3	00043959 099401003495 XXXX
4	00003943 00893942399012 XXXX
5
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	CUST_NO PART_NO OTHER_DATA		CUST_NO	PART_NO	OTHER_DATA
2	00003495 0993940322 XXXX		00003495	0993940322	XXXX
3	00043959 099401003495 XXXX		00043959	099401003495	XXXX
4	00003943 00893942399012 XXXX		00003943	00893942399012	XXXX
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.

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.