Removing zeros from in front of imported data

[Scott Baerman]

I am attempting to compare data from two different databases, using excel.
The databases contain insurance policy numbers. In one database some of the
policy numbers contain two additional zeros at the beginning of the number.
In the other database the zeros do not appear. I'm trying to remove just the
two leading zeros from the column of imported data so that I can then
compare the duplicate entries. I have attempted to select the range of data
and then select "Format" "Cells" and select "Number"... Which I thought
should remove the leading zeros. Unfortunately, it doesn't. Some of the
policy numbers contain letters at the end of the policy number. I can use
the "Value" function that removes the leading zeros, but it gives me an
error on the cells that contain letters at the end of the policy number. Any
idea's on how I can remove just the leading zeros?

 

[A C]

Here is a non-single-step method. Its fairly easily scalable as you simply
repeat the formula in a new column until its culled off all the leading
zeros. So if you have a max of 2 leading zeros then you need to use the
formula in 2 columns, if you have a max of 3 leading zeros then you need it
in 3 columns etc etc.

Lets assume your list of data is in column A

In column B write the formula:

=If(Left(A1,1)="0",Right(A1,Len(A1)-1),A1)
Copy this formula down so every row with a policy number has this formula
next to it.

As an example this will create (where the , indicates different columns and
column A contains the original and dirty policy numbers):
A,B
123, 123
123, 123
00123a, 0123a

So the formula removed the first leading 0 in row 3, and it left the two
entries in rows 1 and 2 alone.

Now simply copy the formula in cell B1 to cell C1, and copy down.
Now you will get:
A,B,C
123, 123, 123
123, 123, 123
00123a, 0123a, 123a

Your data is now clean.

Keep copying this formula over 1 column until all the leading zeros are
gone. To prevent even having to worry about how any leading zeros there are
lets assume your policy number is xx characters long, simply copy it over xx
columns. Even if you only had a max of say 4 leading zeros and the policy
number was 12 characters long the formula doesnt break any numbers which
dont have leading zeros so the last (and unnecessary) 8 columns simply keep
displaying the repeated and clean policy numbers. Doing it this way means
you never have to check your data to figure out how many columns you need,
just bang new data in each time and you know it will work

Hope this helps

Regards
A

 

[Dave Peterson]

If those policy numbers are strictly numeric (1234, not 1234A), you can

select an empty cell
copy it
select the range of policy numbers
edit|Paste special|Check Add.