Is it possible for MS Excel to automatically insert a dash into scanned in numbers?

Question

Let's say for example that I am scanning in barcodes with a scanner into an Excel workbook. These barcodes have numbers yet, if I want to replicate the dashes on the 'barcodes' (not isbn barcodes!) I need to enter those manually for each that I scan in. Is there a way in MS Excel (2003 professional ed.) that I can set it up so that after, say, every 6 digits a dash is inserted? Let me know if this needs more clarification and I'll edit this question.

Answer 1

Excel will automatically put a dash (minus sign) for a negative number, depending on the number format and what it is receiving.

Or are you trying to insert a dash?

Answer 2

The answer above using the MID function is the way to go if you want the barcodes as text with the dashes inserted.

If you want the barcodes to remain as numbers, but just display with the dashes, format the barcode cells with the following custom format: under the Format menu, select Cells, the Number tab, select Custom and enter the following custom format

######-######-######

If above is hard to read, it's 6 #s followed by a dash, followed by 6 #s, followed by a dash, followed by 6 #s.

Above assumes the barcodes are 18 digits long and you want a dash after every 6 digits. Change the number of #s to match the actual barcode length.

Answer 3

If the barcode numbers are all of a certain length then it's certainly possible to stick a dash in every 6 (or whatever) characters using a forumla. As an example, let's say your barcode numbers are all eighteen characters long and you wanted a dash after every six character. If your original barcode number was in cell A1, then the formula to produce the new barcode with the dashes inserted in another cell would be:

=MID(A1,1,6) & "-" & MID(A1,7,6) & "-" & MID(A1,13,6)

Answer 4

Use excel excellent formatting fules
eg
1234567 = 123-4567 when you format
using "000-0000"
There are advantages too.
+No formulas is needed. Numbers takes less memory than strings.
+Adding a dash turn it into a string.
+You can change the format any time
+If you are manipulating cells that contains date, it can be sorted easily.

Answer 5

100000