Recognising text as data

G

Grayling

Hi. A2 contains text like this example

"YORK (12 Jun 2004) "

and I extract to a number of P cells the date part ("12
Jun 2004") using

=MID($A$2,FIND("(",$A$2)+1,FIND(")",$A$2)-FIND("(",$A$2)-
1)

The problem for me is that Excel doesn't recognise the P
cell text (that is, e.g. "12 Jun 2004") as a date, so I
can't reformat it into another date format and can't sort
data on the basis of it. Very grateful for any help with
this.

Grayling
 
F

Frank Kabel

Hi
try
=--SUBSTITUTE(MID($A$2,FIND("(",$A$2)+1,FIND(")",$A$2)-FIND("(",$A$2)-1
)," ","-")
 
J

Json Morin

Dates are nothing more than numbers in Excel, so you need
to convert your date string to a number by multiplying
your formula by 1:

=yourformula*1

and then format the cell as date.

HTH
Jason
Atlanta, GA
 
J

John Woodgate

I read in microsoft.public.excel.worksheet.functions that Grayling
Hi. A2 contains text like this example

"YORK (12 Jun 2004) "

and I extract to a number of P cells the date part ("12
Jun 2004") using

=MID($A$2,FIND("(",$A$2)+1,FIND(")",$A$2)-FIND("(",$A$2)-
1)

The problem for me is that Excel doesn't recognise the P
cell text (that is, e.g. "12 Jun 2004") as a date, so I
can't reformat it into another date format and can't sort
data on the basis of it. Very grateful for any help with
this.
Click to expand...

I think you need to add the DATE function, otherwise your P cells are
treated as text. Try:

=DATE(MID($A$2,FIND("(",$A$2)+1,FIND(")",$A$2)-FIND("(",$A$2)-1))


II that doesn't work, see if A2 is formatted as Text. I've had problems
with dates recently, but the string functions you are using do work IF
the original data is Text.
 
Top