I have a cell containing a seven digit number (A1) that will be used to create a barcode. In order to do this, I need an asterisk at the beginning and at the end of each number (i.e. from 1234567 to *1234567*). Normally I would just type in the asterisks but I'm dealing with over 18000 rows.

I've tried using the following formula in another cell starting at C1 and continuing on down but when the resulting cells are converted to barcodes, the formula is displayed and not the number.

="*"&A1&"*"

Any ideas on how to do this?

Kevin

Hello,

you're on the right track, just copy the formula, edit => paste as... values.

You can also use VBA to automate this task for you, e.g. (for numbers)



Martin



Edited by: KingMartin on Wed May 2 16:23:16 EDT 2007.

The "copy & paste special" worked perfectly. I'll also look into using the VBA code for the next time I have to do this to save time on it.

In the line For Each cl In Range("A:A").SpecialCells(xlConstants), do I need to specify the cell range in ("A:A") with A1:A18000?

Thank you for your help.

Kevin

Hi again Kevin,

no, you don't have to, SpecialCells(xlConstants) will limit the cells in column A to the tightest possible scope,constant values (=> no formulas, blanks, etc.)

Martin