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".
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?
ReplyDeleteYou'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.
DeletePerhaps 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.
DeleteIt 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.
DeleteIn 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