Build an ID from a department and a number
PremiumBadge IDs are the first 3 letters of the department in capitals, a dash and the employee number, like MAR-1042. In C2, build the ID with LEFT, UPPER and &.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Employee no. | Badge ID | |||
| 2 | Marketing | 1042 | ||||
| 3 | Finance | 877 | ||||
| 4 | Sales | 1310 | ||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Joining text with & and TEXTJOIN and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:SUBSTITUTE, TEXTBEFORE and TEXTAFTERDates and DATE
Need help?
Why this works
LEFT(A2,3) takes "Mar" and UPPER turns it into "MAR". The & joins the pieces in order: "MAR", the dash in quotes and the number in B2, which & turns into text. Filled down, the formula gives FIN-877 and SAL-1310.
Common mistake: Leaving out the quotes around the dash. Without them Excel reads - as a minus sign.
The answer
- C2
=UPPER(LEFT(A2,3))&"-"&B2