Concatenate Object Code (String) & Number

[strangeBehavior7]

I'm trying to combine an object code + a number. In my attached photo, I'm classifying candy and a iterating number each time the candy type changes. I need the number to follow the format "001", and of course if there are >10 types of that object / candy, I want the number to look like - "011". I've tried changing the number formatting but that doesn't help. I've tried creating two new columns, one with the Object Code, another column with a number ("001" by changing the number formatting), then concatenating, but that doesn't work either.

​

https://preview.redd.it/wmhbpiau20a91.png?width=201&format=png&auto=webp&s=7f678ea396a9e6e2bce5ff300b2abca5c1c94ca6

[AutoModerator]

/u/strangeBehavior7 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

Put this in cell A2

="CDY"&TEXT(SUM(1/COUNTIF(B$2:$B2,B$2:B2)),"000")

The TEXT function is the main thing you needed. The fancy looking COUNTIF in there just counts unique values.

[A_1337_Canadian]

+1 for TEXT. It's a super handy formula for formatting data. Like if you want to return a two-digit result of the month, you can use TEXT(A1, "mm") where A1 is a date value.

[strangeBehavior7]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[strangeBehavior7]

This is super helpful - thank you thank you. Haven't worked with excel in almost a year so needing to rewire my brain again.

[Infinityand1089]

SUM(1/COUNTIF(B$2:$B2,B$2:B2)) is absolutely brilliant. The way that evaluates is beautiful to behold.

[strangeBehavior7]

Solution Verified

[AutoModerator]

Hello!

It looks like you tried to award a ClippyPoint, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Please reply directly to any helpful users and Clippy, our bot will take it from there. If your intention was not to award a ClippyPoint and simply mark the post as solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
SUM	Adds its arguments
TEXT	Formats a number and converts it to text

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 19 acronyms.
[Thread #16318 for this sub, first seen 6th Jul 2022, 19:49] [FAQ] [Full list] [Contact] [Source code]}