I would suggest creating a new column (e.g. "Unique #") where you can use the Auto-number column type to generate the unique #### number for the project. Then, your Project ID column can build the value based on the Pillar, Unique # and Team fields.
Unique # column with 0000 numerical places in the display:https://us.v-cdn.net/6031209/uploads/Z223M7FA14IQ/unique-23-auto-num.png
For this example, your Pillar column (Dropdown) would look like this:https://us.v-cdn.net/6031209/uploads/WE049UQ5YMIR/c1-pillar-2025-01-28-16-30-29.png
For this example, your Team column would look like this: (Notice that it has the Team "Code" to the left of the | symbol, so that you can just include a short team code in your project ID).
https://us.v-cdn.net/6031209/uploads/TZTV0VVO5HAY/c2-team-2025-01-28-16-36-04.png
Then, your Project ID column could use a column formula to combine all the information:
="P" + LEFT(Pillar@row, 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row)-1)
If you Pillars may grow to 10 or more, you may want to adjust the formula to accommodate 2+ digit numbers:
="P" + LEFT(Pillar@row, FIND(". ", Pillar@row) - 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row) - 1)https://us.v-cdn.net/6031209/uploads/5E5RN97QACSJ/data-2025-01-28-16-55-04.png
Hope this helps!
Automated project ID using 2 column variables
Hi!
I'm trying to create a formula or automate the generation of a project ID but it needs to include 2 separate variables where the information are in 2 separate columns.
Column 1: Has 1 of 5 pillars with the pillar number and text. Below
- Build a community to enable the academic mission
- Create a thriving, fair and diverse University
- Develop next generation leaders
- Strengthen and support talent experience
- Embed exemplary and compliant practices
Column 2: Has the team we want to assign to eg.
CPO - Employment Compliance Directorate
CPO - HR Business Partners
CPO - Office of Indigenous Employment & Development (OIED)
CPO - Organisational Development
CPO - Workplace Relations & Integrity
OCPO
Workforce Strategy & Planning
HR Services
Required END RESULT: P<pillar number><3 digit job number>-<team assigned>
So it would look like P10003-OD
I was told to create a new column for the 2 columns so it creates P1 and then OD THEN concatenate but the 2 additional columns are more manual work. Is there a more automated way to get this automated job number?
Best Answer
-
Hi @Carmen H,
I’ve tested the formulas that @L J provided and they’re working for me - thanks for the great advice, L J!
The formulas will work with dropdown columns and the order of the columns in your sheet won’t affect the formulas. However, if your column names are different to those in the formulas, even including or excluding a space, this could be causing the UNPARSEABLE error.
If you can’t work out what the issue is, could you provide a screenshot of your sheet showing all the column headers that are referenced by the formula, and showing the full formula in the cell as if you’re about to edit it? That should help us work out what’s causing the error.
Thanks,
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I would suggest creating a new column (e.g. "Unique #") where you can use the Auto-number column type to generate the unique #### number for the project. Then, your Project ID column can build the value based on the Pillar, Unique # and Team fields.
Unique # column with 0000 numerical places in the display:
For this example, your Pillar column (Dropdown) would look like this:For this example, your Team column would look like this: (Notice that it has the Team "Code" to the left of the | symbol, so that you can just include a short team code in your project ID).
Then, your Project ID column could use a column formula to combine all the information:
="P" + LEFT(Pillar@row, 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row)-1)
If you Pillars may grow to 10 or more, you may want to adjust the formula to accommodate 2+ digit numbers:
="P" + LEFT(Pillar@row, FIND(". ", Pillar@row) - 1) + [Unique #]@row + "-" + LEFT(Team@row, FIND(" | ", Team@row) - 1)Hope this helps!
-
Hi! Appreciate your feedback.
My cell is coming back with #UNPARSEABLE. Is this because the values must be text? I have the pillar and Team columns as drop down fields.
My columns are also not ordered the same way, does that matter?
Thank you in advance
-
Hi @Carmen H,
I’ve tested the formulas that @L J provided and they’re working for me - thanks for the great advice, L J!
The formulas will work with dropdown columns and the order of the columns in your sheet won’t affect the formulas. However, if your column names are different to those in the formulas, even including or excluding a space, this could be causing the UNPARSEABLE error.
If you can’t work out what the issue is, could you provide a screenshot of your sheet showing all the column headers that are referenced by the formula, and showing the full formula in the cell as if you’re about to edit it? That should help us work out what’s causing the error.
Thanks,
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!