Whither double asterisk-inserting custom Excel formula??

May 6, 2009 2:36 PM

Excel n00b needs help with what ought to be a very simple custom formula. And yes, I have looked around on the interwebs for the answer.

I most humbly submit:

In order for me to print numbers as 'barcode 3 of 9' in a way that my scanner can read it, I have to add asterisks around it when it is still text. For example,

I have: 39020001341877
I need: *39020001341877*

One barcode per cell. It's like doing a find/replace for finding "all 14-digit numbers in all cells in this column" and replace with *asterisks around that number.*
I really need to be able to print them as readable, and I cannot alter how the scanner works, or other non-Excel related variables.

If that makes sense to anyone, and you might have an idea how to fix it, please let me know.

I am using Excel 2007 in Vista.

posted by wowbobwow to computers & internet (6 comments total)

Assuming all your values are in column A1, put this in B1:

="*"&A1&"*"

Then copy and paste down the entire length of column B.
Select column B, hit Copy, select column A, Paste Values.

Someone else might be able to come up with a more elegant answer.
posted by Ziggy Zaga at 2:41 PM on May 6 [1 favorite has favorites]

If A1 contains the number 12345, then set C1 to:

="*" & A1 & "*"

And the result will be *12345* in C1.
posted by Faust at 2:42 PM on May 6

Ok, I don't really know how to do what you want, but I do have a cheesy work around provided you're not working with HUGE amounts of data.

Insert a cell before the column with your numbers, type an * in the first column and then drag to copy it to all the cells in the inserted column. Do the same thing in a column after your column with the numbers. So you have three columns, one that has * in every cell, one with your numbers, and another one that has * in every cell.

Copy the three columns and paste into notepad. Highlight the space between the first asterix and the first number. Go to Edit > replace and paste in the space you copied in the "find what" field and put nothing in the "replace with" field. Hit replace all.

Then just select all and paste back into your excel column with the numbers and delete the asterix columns.

It sounds like a lot of steps, but it will take less than 5 minutes to do (unless you have a lot of data).
posted by Kimberly at 2:47 PM on May 6

If your data is in cell A1, you want this formula in cell B1:
=CONCATENATE("*",A1,"*")

If you have other data in column A and only want the asterisks added if it's 14 digits long, make B1 =LEN(A1) and C1 =IF(B1=14,CONCATENATE("*",A1,"*"),A1)


If you want final cell to be the actual value and not just a formula that displays the value, copy it and then go to Paste Special... and select Values.
posted by soelo at 2:48 PM on May 6

Heh. Or that. What they said above.
posted by Kimberly at 2:48 PM on May 6

Whee! That was the trick. Thank you so so much!
posted by wowbobwow at 2:50 PM on May 6