Leading zeros in a complicated Excel string

[Tirshek]

Hello,

I am looking for help with the following function. I used this function for a digital lot number book for 2017, but we are making some changes and I need to make a new book for 2018. The changes involve some leading zeroes, which are making my formula a bit more difficult. Here is the setup.

=LEFT(D6,4)&(MID(G5,5,3)+1)&"-"&"7"&LEFT(A6,3)

In cell D6 is where my product code will go. These are either 3 or 4 digits, there are 5 different product codes= TQA or TQBG are some examples.

In cell A6 I store the julain date as calcluated by excel when the user enters the date. For example if the user entered 1/1/17 the julain date would be displayed as 001. The left function at the end of the string simply tacks this julain date on the end of the lot number.

In cell G5 is the previous lot number. Note: to start the sheet I would manually enter the first lot number and allow the function to generate lot numbers for me for the rest of the sheet. G5 contains TQGB789-7006.

So if we follow along the first left function will pull over the product code which the analyst will enter into cell D6.

The mid function will pull and add one to the 3 digit lot number. From the above example 789 would becomes 790.

The last bit of the string adds in the "7" for the year and then tacks on the julian date.

So here is my changes and my questions. I am changing from the one digit year to a two digit year. From "7" to "18."

To account for the extra digit we are dropping the T from all of our product codes. So TQA becomes QA.

All of this is an easy fix for me, I have the left and right part of the formula all set. This year we are also resetting the lot numbers back to 001, which is where my issue comes up. Instead of starting on 789 for the year I am starting on 001. The leading zeroes are what is causing my issue. Normally I would use the left function to take the entire string, but in this case I can't due to the different length product codes.

Any help on this would be great, let me know if you need further clarification.

[JohnTopley]

Attach a sample workbook (not image).

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 then scroll down to Manage Attachments to open the upload window.

[Tirshek]

I have attached my 2017 sheet which worked just fine for us. I have also attached the 2018 sheet I have been working on. I could kinda get it done with two left functions, one to hold my zeroes, but I was still hitting issues when changing product codes.

Thanks again for any help,

Jared

[Tirshek]

Note the 2018 sheet still has some carryover from the 2017 sheet. It is a work in progress, but it should show what I am looking for. That initial lot number in cell G5 shows the overall format. I also attempted using the text and value functions since I saw them as answers to fix dropped leading zeroes, but I didn't have much luck with them.

[Tirshek]

I have figured out a work around, but it will take some typing. If you can come up with a fix for the leading zeroes then it would absolutely save some significant time!

I will just use "001" instead of the mid function. I will do this for the first 100 lots to get me to the point where I can use the same formula I used in 2017. This should work for me, but again let me know if you come up with something better!

[Tirshek]

If anyone is curious. Here is the 2018 completed logbook that I was working on. It includes my fix of getting rid of the mid function to just use a sting of text instead. I go back to using mid and find when I hit lot number 100 and no longer have those pesky leading zeroes. For whatever reason I couldn't get value and text functions to work for me.

[Pete_UK]

Try this one in G3 of the 2018 book:

=LEFT(G2,2)&TEXT(MID(G2,FIND("-",G2)-3,3)+1,"000")&"-"&"18"&LEFT(A3,3)

then copy down.

I'm not sure how this will work with 3-letter Product codes - you might like to try this version:

=IF(D3="",LEFT(G2,LEN(LOOKUP("zzz",D$2:D3))),D3)&TEXT(MID(G2,FIND("-",G2)-3,3)+1,"000")&"-"&"18"&LEFT(A3,3)

Hope this helps.

Pete

[Tirshek]

Try this one in G3 of the 2018 book:

=LEFT(G2,2)&TEXT(MID(G2,FIND("-",G2)-3,3)+1,"000")&"-"&"18"&LEFT(A3,3)

then copy down.

I'm not sure how this will work with 3-letter Product codes - you might like to try this version:

=IF(D3="",LEFT(G2,LEN(LOOKUP("zzz",D$2:D3))),D3)&TEXT(MID(G2,FIND("-",G2)-3,3)+1,"000")&"-"&"18"&LEFT(A3,3)

Hope this helps.

Pete

Thanks Pete,

This is what I was looking for. I used your first string, I just changed the 2 to a 3 to account for the 3 digit product codes. I tested with both 2 and 3 digits and this works. I was having issues incorporating the text function, but this is exactly what I was trying to do.

Thanks again!

[JohnTopley]

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

[Tirshek]

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

Already out of the office, but I will be sure to do it tomorrow. I don't see the option on my mobile. Thank you again for the help!

[Pete_UK]

Glad to be of help - thanks for the rep.

There is a problem if you use LEFT(G2,3) at the beginning of the formula, as this will always pick up the product code from the previous row, rather than the code that is in the current row.

Hope this helps.

Pete

[Tirshek]

Yeah I modified it to pull the product code from the correct cell. G2 is now the D6 cell I initially talked about. The cell where the analyst will enter the product code on the line they are working on.

I was testing it, and it should work great for us!

[Tirshek]

Here is the updated file if anyone is curious how it works.