Have you ever received a worksheet where for example the dates were in a weird format?
Not corresponding to anything that can remotely recognised by an excel system, on this side of the moon? 🙂
I certainly did.
SUBSTITUTE is a very handy formula in many situations.
I used it on may occasion when I have to do some text operation.
- You can Remove dots from Mr. and Mrs.
- Gt rid of unwanted characters
- Replace characters
How does the SUBSTITUTE in excel with a formula works? (what does it need?)
=SUBSTITUTE(text, old_text,new_text,[instance_num] – cell that contains the text you want to manipulate
=SUBSTITUTE(text, old_text,new_text,[instance_num] – characters you want to substitute
=SUBSTITUTE(text, old_text,new_text,[instance_num] – substitute characters
=SUBSTITUTE(text, old_text,new_text,[instance_num] – instance number you want to substitute. If not provided it will substitute all instances
In plain English:
For substitute you first have to specify the cell you want to modify, then what is the “old text” and what do you want to replace it to. Check it below.
In this example we used the ISTEXT formulas too.
- As you can see after we substituted the commas to slashes we can see that the cell value is still aligned to the left.
This indicates that the cell’s value is not a value but text.
- We do a quick check on this in Cell F1.
We use the ISTEXT formula. This will omit TRUE or FALSE
- Then we use a simple trick to bring a value that is formatted as text back to a proper value
Multiplying it by one.
- The last step is to change the cell formatting to a proper date format.
We did this by pressing Ctrl+1 to bring up cell formatting.