extracting date from a text

  • Thread starter Thread starter demolay
  • Start date Start date
D

demolay

i want to extract a date from a text. for example, i have the followin
line:
"monday 1 january 1978"
"tuesday 2 january 1978"
...
i want to turn 1-1-1978 or something like that 1178, 111978 or suc
as... day name is not important. i only want numbers...
what formulas should i use for this
 
i want to extract a date from a text. for example, i have the following
line:
"monday 1 january 1978"
"tuesday 2 january 1978"
..
i want to turn 1-1-1978 or something like that 1178, 111978 or such
as... day name is not important. i only want numbers...
what formulas should i use for this?
Click to expand...

Your examples are ambiguous.

But to convert your first into 1178, you can use:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(A1)-FIND(" ",TRIM(A1)))),"dmy")

and to convert it into 111978:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(A1)-FIND(" ",TRIM(A1)))),"dmyyyy")

The ambiguity is because your example gives us no idea as to whether the first
and second "1's" represent day-month or month-day. But you can make the
appropriate changes in the above formulas.


--ron
 
On Thu, 15 Jan 2004 10:30:33 -0600, demolay



Minor change in my initial posting:

But to convert your first into 1178, you can use:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(trim(A1))-FIND(" ",TRIM(A1)))),"dmy")

and to convert it into 111978:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(trim(A1))-FIND(" ",TRIM(A1)))),"dmyyyy")

hmm thanx
what if i want to add the hour?
3:13:00
i want to turn (3) from it..
Click to expand...

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))))+
TIMEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1)))),"dmyh")



--ron
 
On Thu, 15 Jan 2004 10:30:33 -0600, demolay



Minor change in my initial posting:

But to convert your first into 1178, you can use:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(trim(A1))-FIND(" ",TRIM(A1)))),"dmy")

and to convert it into 111978:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(trim(A1))-FIND(" ",TRIM(A1)))),"dmyyyy")



=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))))+
TIMEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1)))),"dmyh")



--ron
Click to expand...


Or, to more literally respond:

=TEXT(DATEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))))+
TIMEVALUE(RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1)))),"dmy(h)")


--ron
 
Back
Top