Removing text from a date
Legend - it works
"Ron Rosenfeld" wrote:
On Thu, 2 Aug 2007 17:20:00 -0700, ScottG
wrote:
Hi
I have a date field in a spreadsheet that is coming from another system as a
text fled and contains charatcetrs I wish to remove. And I want the output to
just be dd/mm/yy. Problem is I can't just count overall characters and remove
left and righ becuase sometimes there is an asterisk and sometimes the date
has 2 characters for the number ie 10 Jun as opposed to 5 Jun. Example
below....
Thu, 5 Jul 07*
Anyone got any cunning plans around a formulae to clean this up and output
just a date?
If the format is similar to the above, in that it always starts with several
letters followed by a comma, and optionally end with an asterisk, then perhaps
this will work:
=--TRIM(MID(A1,FIND(",",A1)+1,FIND("*",A1&"*")-FIND(",",A1)-1))
Format the result as one of Excel's date formats, or else you will only see a
five digit serial number.
--ron
|