How to Add Text to the Start or End of Cells

To add text to a cell in Excel, such as a prefix like "SKU-" or a unit like " kg", you join it to the existing value with the & sign. The formula goes in a helper column, and then you paste the results over the original. This guide shows how to add text to the start, the end and the middle of cells, how to keep numbers looking right, and how to show added text without changing the value at all.

How to add text to the start of a cell#

Put the new text in double quotes, then join the cell with &. Say column A holds product numbers like 4471, and every code should start with "SKU-":

="SKU-"&A2

The result is SKU-4471. The & sign is Excel's join operator: it sticks two pieces of text together, in order. Whatever is inside the quotes appears exactly as typed, including spaces and punctuation.

To do the whole column:

1. In B2, type ="SKU-"&A2 and press Enter.
2. Double-click the fill handle to copy it down.
3. Copy column B (Windows: Ctrl+C; Mac: ⌘C).
4. Right-click A2 and choose Paste Special > Values.
5. Delete column B.

Pasting as values replaces the formulas with the finished text, so you can safely delete the helper column. If you paste over the wrong cells, undo straight away (Windows: Ctrl+Z; Mac: ⌘Z); the guide to editing cells, undo and redo covers the shortcuts.

How to add text to the end of a cell#

Put the cell first and the new text second:

=A2&" kg"

With 12.5 in A2, the result is 12.5 kg. Notice the space inside the quotes, before "kg". Without it, you get "12.5kg". This is the most common small mistake when adding text: Excel adds exactly what's in the quotes, and nothing else.

You can add text to both ends at once: ="("&A2&")" wraps the value in brackets, turning 4471 into (4471).

If the values are numbers with a fixed length, such as IDs that should always have five digits, add the text and the leading zeros together: ="ID-"&TEXT(A2,"00000") turns 4471 into ID-04471. Joining text always produces text, so the new codes sort alphabetically and can't be added up, which is what you want for IDs.

Add text and keep the number format#

When you join a number with &, Excel uses the raw number, not what the cell shows. A cell that displays $1,250.00 really holds 1250, so ="Total: "&B2 gives Total: 1250.

Use TEXT to choose the format inside the formula:

="Total: "&TEXT(B2,"$#,##0.00")

The result is Total: $1,250.00. The second argument of TEXT is a format code:

Dates need TEXT too, because a date is stored as a number. ="Due "&TEXT(C2,"d mmm yyyy") shows Due 15 Mar 2024 instead of Due 45366. The guide to combining text in Excel has more examples of numbers and dates in joined text.

Add text without a formula: custom number formats#

A custom number format adds text to what the cell shows, while the value stays a number you can still add up. This is the best choice for units and labels on numbers.

1. Select the cells.
2. Open Format Cells (Windows: Ctrl+1; Mac: ⌘1).
3. Choose Custom on the Number tab.
4. Type a format in the Type box, then click OK.

Text in a format goes inside double quotes. 0 stands for the number, and @ stands for text. With this method, =SUM() on the "kg" column still works, which it wouldn't if you had joined " kg" with a formula.

Add text in the middle of a cell#

To insert text at a position, use REPLACE and tell it to remove zero characters:

=REPLACE(A2,4,0,"-")

With ABC123 in A2, the result is ABC-123. REPLACE starts at position 4, removes 0 characters, and inserts the dash. To insert text after a certain character rather than a fixed position, use FIND to get the position. For example, =REPLACE(A2,FIND(" ",A2),0,",") adds a comma before the first space. The guide to FIND and SEARCH explains how to locate characters.

Add text only to some cells#

Combine & with IF to add text when a condition is met. To tag big orders:

=IF(B2>10000,A2&" (priority)",A2)

For an order of 12,400, the result is INV-1043 (priority); smaller orders keep their plain code. The guide to IF with text shows more ways to build conditional labels.

Common mistakes when adding text#

1. Missing quotes. =SKU-&A2 gives an error. Added text always goes inside double quotes: "SKU-".
2. Quotes around the cell. ="SKU-"&"A2" returns the letters SKU-A2. Cell references never go in quotes.
3. Forgetting the space. =A2&"kg" gives 12.5kg. Put the space inside the quotes: " kg".
4. Joining text to numbers you still need to add up. The result is text. Use a custom number format instead.

Use Flash Fill for a quick one-off#

Flash Fill can add text by copying a pattern. Type the first result by hand next to the data, such as SKU-4471, start the second, and press Enter to accept the preview, or use Data > Flash Fill (Windows: Ctrl+E). It's quick, but the results don't update when the source changes, so prefer a formula for lists that grow.

Practise it#

Joining text is a skill you'll use in reports, labels and job tests. Practise joining text in a live spreadsheet: you type formulas with &, TEXT and CONCAT, and each one is checked instantly. Or try the label exercise on this page first.