Search for date in text string

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

Fred Smith

I have a text cell which contains a date in the form of ddmmmyy.
Unfortunately, the date can appear anywhere in the cell. I'd like to convert
it to a real date using something like:

=value(mid(a1,x,7))

Is there a search function somewhere which would get me the value of x?
 
I have a text cell which contains a date in the form of ddmmmyy.
Unfortunately, the date can appear anywhere in the cell. I'd like to convert
it to a real date using something like:

=value(mid(a1,x,7))

Is there a search function somewhere which would get me the value of x?
Click to expand...


The *array-entered* formula:

=MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(A1,ROW(INDIRECT("1:255")),7))),0),7)

will get you the value of x. So to convert it to an Excel date, use the
formula:


=DATEVALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(A1,ROW(INDIRECT("1:255")),7))),0),7))

To *array-enter* a formula, after typing or copying in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

Format the result as a date.




--ron
 
-----Original Message-----
I have a text cell which contains a date in the form of ddmmmyy.
Unfortunately, the date can appear anywhere in the cell. I'd like to convert
it to a real date using something like:

=value(mid(a1,x,7))

Is there a search function somewhere which would get me the value of x?
Click to expand...
Fred

Try DATEVALUE(cell reference)

Regards
Peter
 
Thanks Ron.

The formula was off by one character, so I just had to change it to:

=MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(A1,ROW(INDIRECT("1:255"))-1,7))),0
),7)

--
Regards,
Fred
Please reply to newsgroup, not e-mail


 
Thanks Ron.

The formula was off by one character, so I just had to change it to:

=MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(A1,ROW(INDIRECT("1:255"))-1,7))),0
),7)
Click to expand...

I'm glad it worked, but I must have misinterpreted something you wrote if it's
off by one character.

Could you post a sample of the string from which you are extracting the date?

Thanks.


--ron
 
Here's a sample:

Sur Met 2.4% 13Nov04 A Cshb


Your formula returned '<space>13Nov0'

By subtracting 1, I got '13Nov04'

Does Indirect have a base of 0 and Mid a base of 1-- is that the reason?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


 
Here's a sample:

Sur Met 2.4% 13Nov04 A Cshb


Your formula returned '<space>13Nov0'

By subtracting 1, I got '13Nov04'

Does Indirect have a base of 0 and Mid a base of 1-- is that the reason?
Click to expand...

I see the problem. I did not consider that there might be <space>'s in the
string; and DATEVALUE will interpret '<space>13Nov0' as a valid date.

For a more generally applicable formula, I would ensure that does not happen
and would suggest this formula:

=MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(
SUBSTITUTE(A1," ","~"),ROW(INDIRECT("1:255")),7))),0),7)

or to obtain an Excel style date:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(
SUBSTITUTE(A1," ","~"),ROW(INDIRECT("1:255")),7))),0),7))

and format as a date.


--ron
 
Ron:
If I do as follows (and copying your formula in B1 downward) I get:
A
B
1 This is a test and today is 11/27/2004. 11/27/2002
2 This is a test and today is 11/27/2001. 11/27/2002
3 This is a test and today is 11/27/1999. 11/27/2001
4 This is a test and today is 11/27/1962. 11/27/2001

B!:
{=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(SUBSTITUTE(A1,"
","~"),ROW(INDIRECT("1:255")),7))),0),7))}

Any thought?
TIA,,



 
Ron's formula tests for a 7-character date string, which is what I required.
Your date string is 10 characters long. The formula is considering only the
first 7 characters, which is why your year is out. Change the 7 to 10 in
Ron's formula to handle your date format.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


JMay said:
Ron:
If I do as follows (and copying your formula in B1 downward) I get:
A
B
1 This is a test and today is 11/27/2004. 11/27/2002
2 This is a test and today is 11/27/2001. 11/27/2002
3 This is a test and today is 11/27/1999. 11/27/2001
4 This is a test and today is 11/27/1962. 11/27/2001

B!:
{=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(SUBSTITUTE(A1,"
","~"),ROW(INDIRECT("1:255")),7))),0),7))}

Any thought?
TIA,,
Click to expand...
 
Ron:
If I do as follows (and copying your formula in B1 downward) I get:
A
B
1 This is a test and today is 11/27/2004. 11/27/2002
2 This is a test and today is 11/27/2001. 11/27/2002
3 This is a test and today is 11/27/1999. 11/27/2001
4 This is a test and today is 11/27/1962. 11/27/2001

B!:
{=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(SUBSTITUTE(A1,"
","~"),ROW(INDIRECT("1:255")),7))),0),7))}

Any thought?
TIA,,
Click to expand...


What Fred said.

Change the formula to:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(
SUBSTITUTE(A1," ","~"),ROW(INDIRECT("1:255")),10))),0),10))


--ron
 
Thanks Ron and Fred..


Ron Rosenfeld said:
What Fred said.

Change the formula to:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(DATEVALUE(MID(
SUBSTITUTE(A1," ","~"),ROW(INDIRECT("1:255")),10))),0),10))


--ron
Click to expand...
 
Back
Top