How to Add a Prefix or Suffix to Every Cell in Excel

The "add the same text to every cell" problem

You need to prepend VEN- to every SKU. Or append .com to every domain name. Or wrap every cell in quotes. Excel has concatenation but using it on a column is a multi-step shuffle. Here are the options.

TL;DR — Key takeaways

• =A2 & "suffix" or ="prefix" & A2 is the formula approach with a helper column.
• Flash Fill can guess the pattern but is unreliable on edge cases.
• A VBA macro prompts for prefix and suffix, applies in place, and forces text format so leading + or 0 don't get auto-converted.

Method 1: Concatenation formula

="VEN-" & A2 & "-FST"

Helper column, fill down, copy, paste-as-values back over original, delete helper.

The catch: four steps. Plus the result might lose its leading character (like + for phone numbers) when Excel re-interprets the text.

Method 2: Flash Fill

Type the prefixed/suffixed version of the first cell next to the source. Press Ctrl + E.

Excel learns the pattern.

The catch: Flash Fill can guess wrong. For simple prefix/suffix it usually works. For mixed-format data it can get confused.

Method 3: The free VBA macro

Download Add Prefix and Suffix to Cells. Free .xlsm with one macro.

1. Select the range.
2. Alt + F8, pick the macro, click Run.
3. Type the prefix (or leave blank).
4. Type the suffix (or leave blank).
5. Modifies every non-empty, non-formula cell in place.

Critically, the macro forces text format on each modified cell so:

• +1 prepended to phone numbers doesn't get interpreted as a math expression.
• 0 prepended to postal codes doesn't get stripped as leading zero.
• Long string concatenations don't get rendered as scientific notation.

A common scenario: SKU prefix migration

Inventory manager migrating from old ERP to new. New ERP requires SKUs to be prefixed with warehouse code (PHX- for Phoenix, TUS- for Tucson, etc.) and suffixed with category code (-FST for fasteners). Old SKUs are bare numbers.

For each warehouse tab:

1. Select the SKU column.
2. Run Add Prefix and Suffix to Cells.
3. Type prefix (PHX-).
4. Type suffix (-FST).
5. Done.

Versus formula chain: about 1 second per warehouse versus 8 minutes.

Watch out for the leading-character gotcha

If your cells contain pure numbers like 4827 and you prefix with +, the result is +4827. Excel might interpret the result as +4827 (a math expression that evaluates to 4827) instead of the literal text +4827.

The macro forces text format on each modified cell to prevent this. So the + is preserved as a literal character.

Frequently asked questions

Can the prefix or suffix include spaces?

Yes. The InputBox accepts any printable characters. So you can use (USD) (with leading space) or Mr. (with trailing space).

What about formula cells?

Skipped. Modifying formula text would corrupt the formulas. To prefix/suffix formula results, freeze the formulas first with Convert Formulas to Values.

What about empty cells?

Skipped. The macro doesn't add prefix-only or suffix-only entries to blank cells.

Can I prefix with one character and suffix with a different one?

Yes. The macro asks separately. You can have any combination of (prefix, suffix), including blanks for either.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

For wrapping cells in matching characters (like quotes on both sides), use the same macro with prefix and suffix being the same. For changing the case of the modified cells afterward, Change Case to UPPERCASE or lowercase or Convert Text to Proper Case.