Search Not Just Numbers

Loading...

Tuesday, 28 October 2014

Excel Tip: Replacing parts of text strings

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

9 comments:

  1. I realise I am probably being thick but in your example where do you enter this formula? Isn't it easier to just amend the text in A1 by clicking on it or using F2 key to retype the bit that needs changing?

    ReplyDelete
    Replies
    1. You're not being thick. Your approach works well for a one-off adjustment. But if you have 30,000 rows of data that all need editing ecery time you import them, the formula approach will save you a lot of time.

      Delete
    2. Perhaps an example using the formulas on multiple cells would help. I'm not clear either on where the formula is entered or how it would apply to a colum of data.

      Delete
    3. It is good practice in Excel to hold data in columns. You can then have calculated columns to apply the same calculation to each row in the entered or imported columns.

      In the example above, the codes that needed editing would not just be in cell A1, but all the way down column A (depending how much data you have).

      There will likely be different data in columns B, C, etc. so let's say our calculated column will be in column D.

      The formula would be entered in column D on the first row (in the example above this is row 1 (as the formula is looking at A1), however in reality this will often be row 2, with the first row containing headers.

      Once the formula is entered in cell D1, we can copy or fill all of the way down column D. The row number in the formula will automatically update relative to its position, so, when we copy from D1 to D2, the reference in the function will move from A1 to A2. Say we have 100 rows of data, then by the time we get to cell D100, the first formula would read:

      =REPLACE(A100,4,3,"SAL")

      You might find the following two posts helpful:

      http://www.notjustnumbers.co.uk/2012/02/99-of-excel-users-get-this-wrong-how-do.html

      http://www.notjustnumbers.co.uk/2013/05/excel-tip-really-quick-way-to-copy-down.html

      Delete
  2. During a recent week of replica watches integrative medicine study in Arizona, I had rolex replica the opportunity to try laughter yoga. What startedas an uncomfortable display of men and women walking around a room fake laughing replica handbags at one another, quickly erupted into some of the best eye-watering, replica shoes belly-shaking laughter I have experienced in some time. What a breitling replica rush as I left the room; I felt total peace and relaxation replica handbags. I think if we all laughed a rolex replica little bit more, life would be a little easier.

    ReplyDelete
  3. Since a woman would never go out without her replica Mulberry handbags, it becomes very important to pick one that is first rate and reflects her persona.With that thought in mind at taking in to consideration unreasonably exorbitant prices on the original designer bags the makers of replica chanel designer handbags started offering their product to females who have passion for fashion. Designer handbags bring an air of stylish and luxury to any outfit. It is not necessary to buy only a designer handbags, still designer handbags are in high demand among woman from all over the world belonging to all ages and having different income. Naturally, it's most convenient to order fake designer bags on the Web. There's thousands of web-sites offering quality replica designer handbags that come in all imaginable styles and colors. In most cases the fake gucci bags will be delivered to you free but this must be ascertained.Fake designer handbags are sold in much bigger quantities than that produced by famous designers and there's reasons for that.For the best cloth fake designer handbags check out the stunning styles offered by Moschino and Cavalli.There's some great hobo bags among the wholesale Burberry handbags.Small clutch bags metal straps in golden or silver colors are also great for the cocktail parties and romantic dinners as long as you do not have plenty of things to over with you.Cloth replica designer handbags in bright colors with comic prints and flower patterns are ideal for the approaching summer. They go perfectly with casual outfits and are spacious.

    ReplyDelete