Add Prefix and Suffix to Cells

What it does

You have a column. You need to add the same text to the start of every cell, or the end, or both. Excel's =A2&"-suffix" helper-column trick works, but it leaves you with formulas that you have to paste-as-values back over your data, which is three steps and one place to mess up. The macro asks you for the prefix, asks you for the suffix, and rewrites every non-empty cell in place with the new value. Formula cells are skipped so existing logic isn't corrupted, and each modified cell is set to text format so results like +14155550100 don't get auto-converted to a number with a plus sign hidden.

Real-world example

Picture an inventory manager at a 12-warehouse industrial supply company in the middle of a system migration. The old ERP stored SKUs as bare numbers like 4827, 4828, 4829. The new ERP requires every SKU to be prefixed with the warehouse code (so the 4827 from the Phoenix warehouse is PHX-4827) and suffixed with a category code (so a fastener gets -FST at the end, ending up PHX-4827-FST). He has 23,000 SKUs to migrate by Friday.

His current workflow: insert helper column, type ="PHX-"&A2&"-FST" in row 2, fill down 23,000 rows, copy the helper, Paste Special > Values back over column A, delete the helper. Then repeat for Tucson, then Albuquerque, then nine more warehouses. About 8 minutes per warehouse if everything goes smoothly, plus the inevitable two warehouses where he pastes into the wrong column and has to undo five steps and try again. Realistic total: 2 hours of fragile clicking with three near-disasters.

With this macro he opens the Phoenix tab, selects column A, runs the tool, types PHX- for the prefix, types -FST for the suffix, and 1,920 SKUs get rewritten in about 1 second. The popup confirms "Modified 1,920 cell(s)." He repeats for the next 11 tabs, picking the right warehouse and category prefix for each. Total elapsed: about 6 minutes, no helper columns, no near-disasters.

On a 23,000-SKU 12-warehouse migration, expect about 100 minutes saved. The bigger win is no Paste Special slip-ups in the middle of a critical migration.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range of cells you want to modify.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick AddPrefixSuffix, click Run.
3. Type the prefix to add to the START of each cell. Leave blank if you only want a suffix.
4. Type the suffix to add to the END of each cell. Leave blank if you only want a prefix.
5. The macro modifies every non-empty, non-formula cell in your selection and reports the count.

FAQ

Why does it force text format on the modified cells?

Because almost every prefix/suffix use case produces strings that Excel would otherwise misinterpret. Prepending + to a phone number turns it into a math expression. Prepending 0 to a postal code gets the leading zero stripped. Forcing the result to text format prevents these silent corruptions. If you specifically need the result to remain numeric (rare), use a formula approach instead.

What happens to formula cells?

They get skipped. The macro reports a separate count for skipped formula cells in the popup. If you want a formula's result to receive the prefix/suffix, freeze the formulas first with Convert Formulas to Values, then run this macro.

Can the prefix or suffix contain spaces, special characters, or punctuation?

Yes. The InputBox accepts any printable characters. So you can use (USD) (with leading space) as a suffix, Mr. (with trailing space) as a prefix, or any combination of letters, digits, and symbols. Line breaks aren't supported because the InputBox doesn't allow them.

What about empty cells?

Empty cells stay empty. The macro skips any cell where IsEmpty() is true, so you never end up with a cell containing just the prefix or just the suffix. Cells with whitespace-only content are treated as non-empty and get modified, so trim them first with Trim Whitespace From All Cells if needed.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes every modification in one step. Always work on a copy of your file before doing a bulk text rewrite. The macro runs entirely offline. Your data never leaves your machine.