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.

Substitute parts of dates, then convert it to date
Substitute parts of dates, then convert it to date

In this example we used the ISTEXT formulas too.

  1. 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.
  2. We do a quick check on this in Cell F1.
    We use the ISTEXT formula. This will omit TRUE or FALSE
  3. Then we use a simple trick to bring a value that is formatted as text back to a proper value
    Multiplying it by one.
  4. 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.

Hope you liked our post on SUBSTITUTE.
If you have a question drop us a comment below. You can follow us on our facebook page.
We also have a number of posts in our Advanced formulas section too.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]