MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing unwanted Characters in strings.

Posted by Jules Alexander on February 14, 2002 12:08 PM

Hi Folks,

I work with bar codes, or more accurately, the numbers that make up and are contained in bar codes. I use Excel and Access every day and have a small situation with Excel that I am requesting some help on.

My job is to take the bar code data (the numbers, typically a twelve or fourteen digit number) from any given manufacturer along with other date about a product, convert it from the Mfg.’s format (generally an Excel spreadsheet with up to twenty or so fields), and convert it to a proprietary format that is distributed to retailers, warehouses, and others that use bar code data.

Often I will receive a bar code from a manufacturer that includes unwanted data, dashes, alpha characters, spaces and the like.

Typical bar code data ; 0-3150-827787-8

If I highlight a particular field, and do a replace, say a dash with nothing, the leading zero disappear no matter how I format the field.

Typical Result; 31508277878

Any ideas about how to keep the leading zeros when doing a replace?

Thanks tons.


Posted by Mark W. on February 14, 2002 12:19 PM

Format the cell(s) containing a bar code as Text
before you Replace.

Posted by dave on February 14, 2002 12:55 PM

You may wish to try a custom format:

then do your replace.

Posted by Mark W. on February 14, 2002 1:03 PM

Won't work if bar code doesn't have a leading zero...

...such as 3-3150-827787-8.