Find value within a text string

  • Thread starter Thread starter StacyM
  • Start date Start date
S

StacyM

So maybe this question will be a little harder...
I need to find a date that is in a text string. The cell is an automatic
update from the internet that always looks like this: Daily Settlements for
Soybean Futures (FINAL)Trade Date: 08/13/2009. Sometimes the word final will
be preliminary. I need a cell that returns the 08/13/2009 part. Thanks for
your help!
 
Well, if it always at the end of the string you can use RIGHT:

=RIGHT(A1,10)

This will give you a text value, but if you want it as a date just do:

=--RIGHT(A1,10)

or:

=VALUE(RIGHT(A1,10))

and format the cell as a date.

Hope this helps.

Pete
 
It works perfectly, thanks a bunch!

Pete_UK said:
Well, if it always at the end of the string you can use RIGHT:

=RIGHT(A1,10)

This will give you a text value, but if you want it as a date just do:

=--RIGHT(A1,10)

or:

=VALUE(RIGHT(A1,10))

and format the cell as a date.

Hope this helps.

Pete
Click to expand...
 
This presumes that only the "date" part is located after the single colon
(and that there's always a single colon in the textstring)
In B1: =DATEVALUE(MID(A1,SEARCH(":",A1)+1,99))
Format as date to taste

Aha? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Well, if it always at the end of the string you can use RIGHT:

=RIGHT(A1,10)

This will give you a text value, but if you want it as a date just do:

=--RIGHT(A1,10)

or:

=VALUE(RIGHT(A1,10))

and format the cell as a date.

Hope this helps.

Pete

On Aug 14, 3:47 pm, StacyM <[email protected]> wrote:
Click to expand...

Pete,
What does the -- character I keep seeing in formulae here do?
Thanks
Brian
 
Hi Brian,

It's actually a double minus - the first one makes the value negative
and the second makes it positive. It's a quick way of converting a
text value (representing a number or date) into a numeric value. Other
ways include multiplying by 1, or adding zero, like this:

=RIGHT(A1,10)*1

=RIGHT(A1,10) + 0

Hope this helps.

Pete
 
Back
Top