Level 2 · Pick labels with &, UPPER and TEXT

Sometimes the output of a sheet is not a number but a line of text: a pick label, a file name, a subject line. Debra’s pickers need one line per order with four facts on it, and the customer’s full name shouldn’t be printed on the outside of a box. You build the line by joining cells, pieces of cells and bits of fixed text with &.

Joining with &

The & operator glues two values into one piece of text:

=A2&" | "&C2

A2 is the order number 12813, " | " is literal text (space, pipe, space), C2 is web. The result is 12813 | web. Anything in double quotes is copied as is, spaces included, so the spaces around the pipe have to sit inside the quotes.

CONCAT(A2," | ",C2) gives the same result; CONCATENATE is the older name. Most analysts use & because long labels stay readable.

Changing case: UPPER, LOWER and PROPER

Function	web becomes	shirley STEWART becomes
UPPER	WEB	SHIRLEY STEWART
LOWER	web	shirley stewart
PROPER	Web	Shirley Stewart

Debra wants the channel in capitals so it stands out: UPPER(C2). PROPER is for names typed in the wrong case; it is blunt (mcdonald becomes Mcdonald), so check what it does to your data.

First name and initial: functions inside functions

D2 holds the full name, Shirley Stewart. The space is the delimiter, the same idea as the @ in the last level. FIND(" ",D2) is 8, so:

Piece	Formula	Result
first name	LEFT(D2,FIND(" ",D2)-1)	Shirley
last initial	MID(D2,FIND(" ",D2)+1,1)	S

MID starts one character after the space and takes 1 character. Then join them with a space and a period: LEFT(…)&" "&MID(…)&"." gives Shirley S.. A function used as another function’s argument is called nesting: FIND sits inside LEFT, and the answer of one becomes the input of the other, with no helper column in between.

Dates in a label need TEXT

Glue the date the same way and something odd happens:

=A2&" | "&B2

returns 12813 | 46013.29…, with the digits running on. The cell B2 shows 2025-12-22 06:58, but that is only its format. Underneath, a date is a count of days since the start of 1900, and the time is the fraction of a day: 6:58 in the morning is 0.29 of a day. & takes the value, not the format.

TEXT(value, format) applies a format and returns the result as text. =TEXT(B2,"yyyy-mm-dd") gives 2025-12-22. The format codes are the ones you would type in Format Cells: yyyy the year, mm the month, dd the day, mmm a short month name, ddd a short weekday. TEXT(B2,"ddd mmm d") gives Mon Dec 22.

The whole label

E2   =A2&" | "&UPPER(C2)&" | "&TEXT(B2,"yyyy-mm-dd")&" | "&LEFT(D2,FIND(" ",D2)-1)&" "&MID(D2,FIND(" ",D2)+1,1)&"."

Compare E2 with Debra’s example in G2 character by character: 12813 | WEB | 2025-12-22 | Shirley S.. Then fill down to row 28. The last order, 12877 from the marketplace on the 24th, should read 12877 | MARKETPLACE | 2025-12-24 | Angela A..

Build long formulas in pieces. Start with =A2&" | "&UPPER(C2), press Enter, check it, then add the next piece. A missing quote is much easier to find in a short formula.

Common mistakes

• The raw date. &B2 prints the serial number. Wrap every date in TEXT before joining it.
• Spaces outside the quotes. A2 & "|" & C2 gives 12813|web: the spaces around & are ignored, only spaces inside quotes end up in the text.
• The initial from the wrong place. MID(D2,FIND(" ",D2),1) starts on the space itself and returns the space. Add 1.
• Month vs minutes. In a format with hours, mm after hh means minutes: "hh:mm". On its own or next to yyyy and dd, mm is the month.