extracting values from string

**Alexander_Golinsky** · 06-19-2012, 04:03 AM

Hi all!

I have a row with a strings. The typical string is
"The Bonds shall be payable in three equal annual instalments on 30th November 2010, 2011 and 2012, and bear a semi annual coupon. Offert date:14.11.2008 Price:86.33613167, Offert date:18.11.2008 Price:83.54645761, Offert date:05.12.2008 Price:79.76367227,"

In some cell there is a date. The question is how to extract the earliest offert date from the above string which is greater than the date in some some cell.

I'm looking for the single formula. Anybody that can help, thanks a lot in advance!!!

**RobertMika** · 06-19-2012, 04:35 AM

Originally Posted by Alexander_Golinsky

Hi all!

I have a row with a strings. The typical string is
"The Bonds shall be payable in three equal annual instalments on 30th November 2010, 2011 and 2012, and bear a semi annual coupon. Offert date:14.11.2008 Price:86.33613167, Offert date:18.11.2008 Price:83.54645761, Offert date:05.12.2008 Price:79.76367227,"

In some cell there is a date. The question is how to extract the earliest offert date from the above string which is greater than the date in some some cell.

I'm looking for the single formula. Anybody that can help, thanks a lot in advance!!!

what do youmean by:
"which is greater than the date in some some cell"?

**martindwilson** · 06-19-2012, 04:42 AM

i dont think you will as match will not accept the calculations neccesary to extract the dates
best i can do is
with that info in a1 in b1:c1
=DATEVALUE(SUBSTITUTE(MID(SUBSTITUTE($A$1,"date:","^",3),FIND("^",SUBSTITUTE($A$1,"date:","^",3))+1,10),".","/"))
=DATEVALUE(SUBSTITUTE(MID(SUBSTITUTE($A$1,"date:","^",2),FIND("^",SUBSTITUTE($A$1,"date:","^",2))+1,10),".","/"))
=DATEVALUE(SUBSTITUTE(MID(SUBSTITUTE($A$1,"date:","^",1),FIND("^",SUBSTITUTE($A$1,"date:","^",1))+1,10),".","/"))
then in say h1
=INDEX(B1:D1,MATCH(E1+1,B1:D1,-1))
put your date to look for in e1

**Alexander_Golinsky** · 06-19-2012, 05:48 AM

Martin, Thank you a lot! You're true guru. I'm pretty sure that there is no better solution. Thank you again!

**Alexander_Golinsky** · 06-19-2012, 06:03 AM

But on my comp, in ur file datevalue doesn't work and gives me #value! error

**Alexander_Golinsky** · 06-19-2012, 06:04 AM

sure it's possible to do the same with date function, but why it works for you and doesn't for me?

**Alexander_Golinsky** · 06-19-2012, 06:11 AM

i got it. your comp uses European date format and mine - American, so mine thinks that 18 is month and displays the error. I'll go fro date function

**FixandFoxi** · 06-19-2012, 06:17 AM

Alternative solution... single formula as requested... as attached :-)

**martindwilson** · 06-19-2012, 06:37 AM

@fixandfoxi 17-Dec-08 gives value error

**FixandFoxi** · 06-19-2012, 07:43 AM

Not sure what you mean. See attached screenshot, my 17-Dec-2008 is working...?

**martindwilson** · 06-19-2012, 08:44 AM

hmm but the answer should be 18th surely
besides which you don't know how long the price string will be that could affect the position of the date.

**FixandFoxi** · 06-19-2012, 09:19 AM

I don't think so. When the "Reference Date" is 19.11.2008, then the result should be 05.12.2008. Because this is the date "which is greater than the date in some some cell". So the 18.11.2008 would be smaller. Hmm, this is my understanding from the original request above.

You can amend the string by its length and this does not change the result. I searched for the term "Offert date:" and captured then the following, relevant characters for the date. Just try!!! If I would go for the position of the date only, yes, then I would have this problem...

**martindwilson** · 06-19-2012, 11:10 AM

nope see attached i changed the prices

**FixandFoxi** · 06-20-2012, 07:33 AM

Good to see that you have no longer this error value! I have updated the formula in order to absorb unexpected, multiple changes in the text/prices... as attached... Thanks!

extracting values from string

LinkBack

Thread Tools

Rate This Thread

Display

extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Re: extracting values from string

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions