From the Not Just Numbers blog:

I have previously posted about using the Find and Replace facility to replace parts of text strings or formulae. But what if you want to be able to do this kind of thing automatically?

You’re going to need to do it with formulae. The good news is that Excel has a couple of functions to do this.

Say you need to replace a section of a nominal ledger code (maybe a section the denotes a department or cost-centre).

If this part of the code will always be in the same place then we can use the REPLACE function.

The syntax for the REPLACE function is:

=REPLACE(OldText,StartNumber,NumberOfCharacters,NewText)

where,

OldText is the text we want to amend

StartNumber is the position in OldText at which we want to start replacing

NumberOfCharacters is how many characters of OldText we want to replace

NewText is the text we want to replace them with

So, say we wish to replace characters 4 to 6 of the string in A1 with the letters SAL, then we can use:

=REPLACE(A1,4,3,”SAL”)

We may, however, not be able to rely on the department being in the same place. We can use the SUBSTITUTE function to replace a particular string with another.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(Text,OldText,NewText,[Instance])

where,

Text is the text string that we want to amend

OldText is the text string (within Text) that we want to replace

NewText is the text string that we want to replace OldText with

Instance is an optional field that can be used to specify which instance (as a number) of OldText should be replaced. If this is omitted all instances of OldText are replaced with NewText

So, if in our earlier example we know the existing code has a department of ADM, then we can use:

=SUBSTITUTE(A1,”ADM”,”SAL”)

This will replace every instance of ADM with SAL, so be careful.

We can use the Instance argument, if we know that it is always the first instance for example:

=SUBSTITUTE(A1,”ADM”,”SAL”,1)


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