Tuesday 3 March 2015

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".

1. Quick way to and paste back a selected range as Values is to drag an edge of the range away with the right mouse button pressed, drag it back again, THEN release the right mouse button and select "Copy Here as Values Only" from the shortcut menu that appears
It may sound like a palaver but it's really quick and easy

Jim

1. Thanks Jim. Yes that's a nice easy one when you know how!

2. @Jim,

Be careful do that! Any formulas which are looking at the cell you need to copy and paste may be changed to refer to the cell which you dragged the data to. A safer option may be to just copy the cell and paste special values directly over the top.

go back and read my comment again; cells are not being pasted elsewhere, it's doing exactly as you suggest, only quicker

jim

2. THANKS IT IS VERY USEFUL

3. If I have eg. Lemons, Oranges, Grapes, Pineapples in A1, A2, A3 & A4, how do I add 01. to 04. so that it makes it 01. Lemons, 02. Oranges, 03. Grapes and 04. Pineapples ?

1. Try:
=FORMAT(ROW(A1),"00")&". "&A1

ROW returns the row number from A1 and the FORMAT function turns it into text using the number format "00", ensuring we have a leading zero if it is only a single digit.

4. FORMAT is a vb function, not a worksheet function. TEXT is the function you meant.

1. Well spotted, you're exactly right. Thanks for that. I obviously had my VBA head on when I replied to that one! It should read:

=TEXT(ROW(A1),"00")&". "&A1