Removing text from a date

[Guest]

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?

 

[Ron Rosenfeld]

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

 

[Roger Govier]

Hi

One way
=--(MID(SUBSTITUTE(A1,"*",""),FIND(",",SUBSTITUTE(A1,"*",""))+2,LEN(A1)-1))

 

[Guest]

Legend - it works

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

 

[Guest]

=SUBSTITUTE(REPLACE(A1,1,FIND(",",A1)+1,""),"*","")+0