Excel Tip: A quick way to add a prefix or suffix to all cells in a list

Do you ever have those situations where you need to make an amendment to the text in every cell in a range?

I know it is an issue I come across regularly. I may need to prefix all nominal ledger codes with a department reference, say, or maybe add "/1" to all existing codes in a list, so that we can add new variations later of /2, /3, etc.

We could obviously rattle down the list, hitting F2 and pasting the additional text at the start or end of each cell. However, we can be a little cleverer than that.

Say we have the second situation described above and want to add "/1" to every cell in the list. We can do this as follows.

If the list starts in cell A1, we could put the following formula in cell B1:

=A1&"/1"

This earlier post explains how to use "&" to join text together, as well as some other useful methods for manipulating text.

If we copy our formula down the whole list, column B will show the new text. We can then copy column B and paste it into column A. We will, however, need to do this using Paste Special, As Values. The easiest way to do this is to copy as normal but when pasting, right-click cell A1 and click the clipboard with "123" on it.

This will paste the results of the formula in column B (as opposed to the formula itself) as text into column A (replacing the original text). We can then delete column B (which will now be showing "/1" twice in each row as it is being added to the new text in column A).

We can use the same technique with different versions of the formula to make any number of amendments to the text.

Say we want to add a department code before the code in column A and separate the two with a hyphen, we can enter the department codes in column B and enter the following formula (in column C):

=B1&"-"&A1

Use this technique with other text manipulation functions, from the post mentioned above, to make pretty much any change you need.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".