Formula Help

AussieVic

Active Member
Joined
Jan 9, 2009
Messages
364
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>
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Code:
=MID(A1,FIND(B1,A1),LEN(B1))
Where A1 is the String and B1 holds what you wish to extract

lenze
 

AussieVic

Active Member
Joined
Jan 9, 2009
Messages
364
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Ignore me...
 

AussieVic

Active Member
Joined
Jan 9, 2009
Messages
364
Code:
=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

Well-known Member
Joined
Jul 24, 2007
Messages
938

ADVERTISEMENT

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:
Code:
=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.
 
Last edited:

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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
AB
1Rob December FirstDecember
2John October 12October
3Credit Interest November 2009November
Sheet5
Cell Formulas
RangeFormula
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

Active Member
Joined
Jan 9, 2009
Messages
364
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:
Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top