Join 3,372 readers in helping fund MetaFilter (Hide)


Whither double asterisk-inserting custom Excel formula??
May 6, 2009 2:36 PM   Subscribe

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 answers total) 1 user marked this as a favorite
 
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, 2009 [1 favorite]


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, 2009


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, 2009


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, 2009


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


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


« Older I have a Lovesac (circa 2003) ...   |  I have been loaned a set of me... Newer »
This thread is closed to new comments.