Text function
REPLACE
Change characters at this position.
Purpose
Change characters at a known position
Returns
The value with the characters at that position swapped out
Syntax
=REPLACE(text, start, how_many, new_text)
| Argument | Required | What goes here |
|---|---|---|
text | Required | The cell holding the text to change, usually A2. |
start | Required | Which character the change starts at, counting from 1. |
how_many | Required | How many characters to take out from there. |
new_text | Required | What to put in their place, in double quotes. Use "" to take them out. |
How to use REPLACE
REPLACE works by position, not by content: start here, take out this many characters, put this in instead.
That makes it the right tool when there is nothing to search for. Blanking the first three characters of every reference does not need them to be the same three.
Give it 0 as the number of characters and it inserts without deleting anything, which is how a separator gets pushed into a code that arrived without one.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=REPLACE(A2,1,3,"")Take out the first three characters. Replacing with nothing deletes. | 6100-8801 |
=REPLACE(A2,1,2,"PL")Re-tag the reference for a different ledger without touching the rest of it. | PL-6100-8801 |
=REPLACE(A2,1,0,"OLD-")Zero characters means insert. Nothing is removed, the new text is simply pushed in at that position. | OLD-GL-6100-8801 |
Notes
- If the values are not all laid out the same way, REPLACE cuts in the wrong place quietly. SUBSTITUTE is safer when there is a marker to match.