Formula Help

[AussieVic]

Hi i need a formula that will extract string November, now i know i could use MID, but that would mean i would have to manually count "Start num" and "Num chars", easy there an easier approach than this?

<TABLE style="WIDTH: 139pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=185><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2145561 height=17 width=185>Credit Interest November 2009</TD></TR></TBODY></TABLE>

 

[schielrn]

I assume you are going to be extracting more than just November? Are you wanting to extract a month out of the string and not always November?

 

[lenze]

=MID(A1,FIND(B1,A1),LEN(B1))

Where A1 is the String and B1 holds what you wish to extract

lenze

 

[AussieVic]

I assume you are going to be extracting more than just November? Are you wanting to extract a month out of the string and not always November?

That is correct, I will be extracting various months, but the mid formula is still manual cos you have to count manually

 

[Jonmo1]

Ignore me...

 

[AussieVic]

=MID(A1,FIND(B1,A1),LEN(B1))

Where A1 is the String and B1 holds what you wish to extract

lenze

That would still mean i would have to physicall type out November in B1 or any other string which i might want to extract

 

[gardnertoo]

Will the string "Credit Interest " always be the beginning? Or does that vary too? If the format will always be "word_word_TARGETWORD_something else" (two words and spaces before the target word and a space with anything else after it), then this formula works on a string in A1:

=MID(A1,1+FIND("^",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2)),FIND("|",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2))-FIND("^",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2)))

The SUBSTITUTE functions drop placeholder characters into the string before and after the target word, then calculate for you the positions of the first letter and last letter of the target word.

 

[lenze]

That would still mean i would have to physicall type out November in B1 or any other string which i might want to extract

Yeah!! But so what? If it's only months, create a DV in B1 with a dropdown list. No matter what you choose to use, you are going to have to tell Excel what you are looking for!!

lenze

 

[schielrn]

That is correct, I will be extracting various months, but the mid formula is still manual cos you have to count manually

This is one way, but there is probably a better way to do it:

Excel Workbook
	A	B
1	Rob December First	December
2	John October 12	October
3	Credit Interest November 2009	November
Sheet5

Cell Formulas
Range	Formula
B1	=TEXT(SUMPRODUCT((ISNUMBER(FIND(TEXT(DATE(2009,ROW($1:$12),1),"mmmm"),A1)))*(TEXT(DATE(2009,ROW($1:$12),1),"m")))&"/1/2009","mmmm")

Hope that helps.

 

[AussieVic]

Yes you are correct, your formula is sooo big, it works, but i aint got a clue how it works

Will the string "Credit Interest " always be the beginning? Or does that vary too? If the format will always be "word_word_TARGETWORD_something else" (two words and spaces before the target word and a space with anything else after it), then this formula works on a string in A1:

=MID(A1,1+FIND("^",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2)),FIND("|",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2))-FIND("^",SUBSTITUTE((SUBSTITUTE(A1," ","^",2))," ","|",2)))

The SUBSTITUTE functions drop placeholder characters into the string before and after the target word, then calculate for you the positions of the first letter and last letter of the target word.