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?

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

Hi

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

--
Regards
Roger Govier



"ScottG" wrote in message
...
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?

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

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


"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?