Adding increasing values to numbers with dashes

Hello again, Excel Forum! Long time no see. I'm working on a new project for my new employer and am trying to streamline a process. Is there a way to take a number sequence (15-00000) and add to it (15-00001, 15-00002, etc) using basic functions?

This final number is being converted into a bar code for cover sheet purposes. Right now, we are physically typing each number in by hand and then printing. If I could make it print a series (say, 50 at a time), that would extremely help our productivity. I'm being told that the number HAS to be all in on cell to make sure the bar code is being created correctly.

If A1 contains 15-00000

Try
=LEFT(A1,3)&TEXT(RIGHT(A1,5)+1,"00000")

That works. Now if I can just find out who made this Excel document so I can get the password to unlock it... <sigh> I may just end up recreating it myself.

You're welcome.

If A1 and A2 contain consecutive numbers like 15-00001 and 15-00002, then you can just select both cells and drag down the fill handle (the bottom right corner of the cursor) to increment them over the range that is dragged.

Hope this helps.

Pete

Ok, let me throw a small wrench into things. I discovered the bar code they're using has an asterisk before and after the number value. How can I put those together in the same cell?

The barcode will read "*15-00001*", but the only editable part needs to be the numbers.

Try
=LEFT(A1,4)&TEXT(MID(A1,5,5)+1,"00000")&"*"

Originally Posted by Jonmo1

Try
=LEFT(A1,4)&TEXT(MID(A1,5,5)+1,"00000")&"*"

That's not getting the barcodes to match. Looks like it's not registering the asterisk in the front, and it's changing something in the middle, but I'm not sure what.

Works for me
Can you post a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Attached is the sample sheet. Column A is the number to be converted. Column C is the first asterisk, D is the converted number, E is the second asterisk. I need the asterisks to be included in the converted number. Make sense?

oh, ok.
I thought you meant the original string (in A) already contained the *'s

Try

="*"&LEFT($A$1,3)&TEXT(RIGHT($A$1,5)+ROWS($A$1:$A1),"00000")&"*"

We are getting there. Now we are off a number.

I've entered the code you gave me in Row 1. I've COPIED the number in A1 to D2. With the barcodes centered, notice how the last part of Row 1 doesn't match the supposedly same value of Row 2?

I don't understand what you're doing in column D...

My suggestion is based on the original value in A1
15-12345

Then in A2, and filled down
="*"&LEFT($A$1,3)&TEXT(RIGHT($A$1,5)+ROWS($A$1:$A1),"00000")&"*"

Will produce
*15-12346*
*15-12347*
etc

See here
EFGunslinger.xlsx

I was using Column D to show what the original code you gave (D1). D2 is the number reflected in A1 to show they were different than what is in D1. What is currently reflected in D2 is what I need reflected in D1, just with the addition of an asterisk on each side. I'm not sure where in the code of D1 I can change that.

ok, so your codes already exist in column A
You just want the *'s added in column D ?

IN D1
="*"&A1&"*"
Fill down

Originally Posted by Jonmo1

I don't understand what you're doing in column D...

My suggestion is based on the original value in A1
15-12345

Then in A2, and filled down
="*"&LEFT($A$1,3)&TEXT(RIGHT($A$1,5)+ROWS($A$1:$A1),"00000")&"*"

Will produce
*15-12346*
*15-12347*
etc

See here
Attachment 416965

Ok, I see. Your code is to populate the rest of Column A.

Originally Posted by Jonmo1

ok, so your codes already exist in column A
You just want the *'s added in column D ?

IN D1
="*"&A1&"*"
Fill down

Doh. That did it. Yes, I need to be able to enter the number without asterisks, but have the barcode display WITH asterisks. That did it. Thanks everyone. I might have more questions later about other aspects of this sheet, but I'll start a new thread about those.

You're welcome.

Well, If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete