The Replace formula
Definition
This formula will replace a character with something you specify. If want to remove a blank we can do this by thus formula.
The makeup of the formula
This formula has 4 fields
- The first field is the text from which you want to remove the character/s.
- The second is where excel must start to remove characters in the text
- The third is how many character must be removed
- The last is with what must the removed places be replaced
Example
Nr |
Text |
Formula |
Result |
1 |
The hills have eyes |
=REPLACE(B2,4,1,"") |
Thehills have eyes |
2 |
Excel is easy |
=REPLACE(B3,6,1,"") |
Excelis easy |
3 |
division 3000 |
=REPLACE(B4,9,1,"") |
division3000 |
4 |
centre 2344 |
=REPLACE(B5,7,1,"") |
centre2344 |
If we want to remove the first spaces of the word, we can use this formula. In the first example, we want to remove the space after the word “the”.
The text is in cell b2 (Yellow). The space starts at the fourth place (blue). We only want to remove 1 space (pink). And we want to insert a blank space (green).
The formula function bar
Things you can do with the Replace formula
- Replace data automaticallt without having to find and replave every month...contact us for more info
<-----Back to index
Next topic----->
