Text Block Dates

[aaronxv]

Hi all,

I have a block of text in a cell, this block will contain 1 or more dates. They may be formatted as January 2017 or 01/01/2017. I would like to write a formula to replace the first style with JAN2017 and the second style 01JAN2017. I am trying to work out a way to do it combining substitute/find, but so far can't figure out the syntax.

 

[Rick Rothstein]

Here is one way to get the output you asked for...

=TEXT(A1,CHOOSE(RIGHT(CELL("format",A1))-2,"ddmmmyyyy","mmmyyyy"))

but note that the output from this formula is no longer a date, rather, it is purely text.

 

[aaronxv]

I tried this, but it gives me a #VALUE error.

To clarify, I will have a cell that contains:

"Random text and information etc, etc, etc, 09/26/2017 more text more text August 2017 more text"

 

[aaronxv]

I have found a solution, thank you for your response.