Preceeding zero in a UPC

  • Thread starter Thread starter Spaceace
  • Start date Start date
S

Spaceace

Greetings,

We are using a file that is exported to an Excel file. The first column is
a UPC. There are a large number of rows as we are dealing with a large
number of products (approx. 200).
The number in the column looks like this;
00005530012345

I need to get this to look like
05530012345. Basically dropping off the first 3 zero's

I have tried using the 'Text to Columns' feature - Fixed width, where I
specified I want to place the first 3 numbers (000) into column B and have
the UPC end up in Column C that will look like 05530012345.

Every time I do this, the number that is in column C is 5530012345.

All zero's have been removed.

How can I change this column to keep the preceding zero?

Any help would be appreciated.
____________________________
Dean F. Hewer
 
Hi Dean..........

As part of the "Text to columns" procedure, just make sure you format the
columns as TEXT...........

Vaya con Dios,
Chuck, CABGx3
 
Hi Dean,

After you select your field width for your column breaks, in the next
window choose "Text" for your "Column Data Format" after selecting both
columns in the "Data Preview" window.

Hope this helps!
 
Nope, that did not work.

I ended up with the three zero's in column B (000) and the 5530012345 in
Column C.
Trying to go from 00005530012345 to 05530012345.

Is there a formula that can be used?

Thanks
 
Hi Dean..........

Try Dominic's more detailed instructions..........it should work.........

otherwise, maybe this will help

=0&C1

to put one zero back in front of the cell with 5530012345

Vaya con Dios,
Chuck, CABGx3
 
yes....that worked. Thanks!!!! 8>)


CLR said:
Hi Dean..........

Try Dominic's more detailed instructions..........it should work.........

otherwise, maybe this will help

=0&C1

to put one zero back in front of the cell with 5530012345

Vaya con Dios,
Chuck, CABGx3





where
Click to expand...
 
With regards to "Text to Columns", it will definitely work. In the
"Data Preview" window, first select Column A > select "Text" in "Column
Data Format", then select Column B > again, select "Text" in "Column
Data Format".
 
yes this did work perfectly. I apologize as I did not read your
instructions in detail.

Thank you...this has saved us multiple steps...

Cheers
DH
 
Dean Hewer said:
yes this did work perfectly. I apologize as I did not read your
instructions in detail.

Thank you...this has saved us multiple steps...

Cheers
DH
Click to expand...

Glad to hear you've got it sorted!

Cheers!
 
Back
Top