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>

 

[Jonmo1]

This will extract whatever month appears in the string in A1

=LOOKUP(2,1/ISNUMBER(FIND(TEXT(DATE(1,ROW($A$1:$A$12),1),"mmmm"),A1)),TEXT(DATE(1,ROW($A$1:$A$12),1),"mmmm"))

DO NOT change the $A$1:$A$12. It has nothing to do with your data, it is used to generate the 12 months.

If there is more than 1 month in the string, it will return the first from the left
If there are no months in the string, it will return #N/A

 

[schielrn]

Here is a little shorter variation of mine in columns B and C:

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

[/B]

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/09","mmmm")
C1	=TEXT(SUMPRODUCT(--ISNUMBER(FIND(TEXT(DATE(2009,ROW(1:12),1),"mmmm"),A1)),--TEXT(DATE(2009,ROW(1:12),1),"m"))&"/1/09","mmmm")

[/B]Haha nevermind it is the same formula.

 

[gardnertoo]

Here's how: I take your string "Credit Interest October 2009" and first SUBSTITUTE the character "^" for the 2nd space, resulting in this string "Credit Interest^October 2009". Then I do another SUBSTITUTE against that result, replacing the 2nd space (which used to be the 3rd space, but with one replaced ahead of it is now 2nd) in that string with a different character, "|", resulting in this string "Credit Interest^October|2009". Then I use the FIND function to locate those two placeholder characters to determine the beginning and end of the word in question.

 

[Jonmo1]

=LOOKUP(2,1/ISNUMBER(FIND(TEXT(DATE(1,ROW($A$1:$A$12),1),"mmmm"),A1)),TEXT(DATE(1,ROW($A$1:$A$12),1),"mmmm"))

If there is more than 1 month in the string, it will return the first from the left

Correction, if there is more than one month in the string, it will return the month that is latest in the year...

IE if th string contains both March and August, it returns August
If the string contains November and February, it returns November.

 

[AussieVic]

Correction, if there is more than one month in the string, it will return the month that is latest in the year...

IE if th string contains both March and August, it returns August
If the string contains November and February, it returns November.

Thanks mate, I really need to learn on my formula techniques to understand how your formula works.

 

[Jonmo1]

No problem...here's how it works...
Row returns the row # of a cell reference

This
DATE(1,ROW($A$1:$A$12),1) - DATE(Year,Month,Day)
Creates an ARRAY of Dates using Year 1 or 1901 (doesn't matter what year), Day 1 and the month corresponding to each row # 1-12.
So the array contains 12 dates {Jan 1 1901, Feb 1 1901, Mar 1 1901, etc, Dec 1 1901}
That array is created twice in the formula
So that breaks the formula down to this
=LOOKUP(2,1/ISNUMBER(FIND(TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm"),A1)),TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm"))
The text function turns those dates into each date's month name
TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm")
So the array now contains just the month names
{"January","February","March", etc, "December"}

So now the formula is broken down to
=LOOKUP(2,1/ISNUMBER(FIND({"January","February",etc,"December"},A1)),{"January","February",etc,"December"})

Here's where it get's more complicated..
Find searches for each month name (January,February,et) in the cell A1. And returns the position # where it was found.
find creates an array holding all the position numbers where each one is found.
If the month is not found, find returns an error #VALUE!
so you now have an array of either #VALUE! or some position # where the month was found
Say A1 is
Credit Interest November 2009
November is at the 17th character
So find is returning this array
{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,17,#VALUE!}
So the formula is now
=LOOKUP(2,1/ISNUMBER({#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,17,#VALUE!}),{"January","February",etc,"December"})
ISNUMBER returns a TRUE or FALSE response based on the test if each value is a number or not.
Errors are not numbers
so now your formula looks like this
=LOOKUP(2,1/{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE},{"January","February",etc,"December"})
When doing a mathematical operation on a True or False value like 1/FALSE or 1/TRUE, the TRUE/FALSE is converted to 1/0 1=True, 0=False
So it's now
=LOOKUP(2,1/{0,0,0,0,0,0,0,0,0,0,1,0},{"January","February",etc,"December"})
Now it's going to create an array of the result of 1 Devided by each number in the array.
1/1 = 1
1/0 = #DEV/0!
so now the formula is
=LOOKUP(2,{#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,1,#DEV/0!},{"January","February",etc,"December"})
Now lookup wants to find the closest match for 2 in that array.
The closest match is 1, which is in the 11th position of the array
So it returns the 11th postion of the 2nd array = November.

Hope that helped, and hasn't fried your brain too much...

 

[Jonmo1]

Also not that this formula is Case sensitive...
Meaning, it will find December, but not december or DECEMBER

To make it NOT case sensitive, just change the word FIND to SEARCH

 

[AussieVic]

Wow thanks for the explanation, still bit confusing but thats cos i need to get more experience in using formulas, i can use Index/Match and sumproduct, but find it difficult to nest with other formulas as you have done below

No problem...here's how it works...
Row returns the row # of a cell reference

This
DATE(1,ROW($A$1:$A$12),1) - DATE(Year,Month,Day)
Creates an ARRAY of Dates using Year 1 or 1901 (doesn't matter what year), Day 1 and the month corresponding to each row # 1-12.
So the array contains 12 dates {Jan 1 1901, Feb 1 1901, Mar 1 1901, etc, Dec 1 1901}
That array is created twice in the formula
So that breaks the formula down to this
=LOOKUP(2,1/ISNUMBER(FIND(TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm"),A1)),TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm"))
The text function turns those dates into each date's month name
TEXT({Jan 1 1901, etc, Dec 1 1901},"mmmm")
So the array now contains just the month names
{"January","February","March", etc, "December"}

So now the formula is broken down to
=LOOKUP(2,1/ISNUMBER(FIND({"January","February",etc,"December"},A1)),{"January","February",etc,"December"})

Here's where it get's more complicated..
Find searches for each month name (January,February,et) in the cell A1. And returns the position # where it was found.
find creates an array holding all the position numbers where each one is found.
If the month is not found, find returns an error #VALUE!
so you now have an array of either #VALUE! or some position # where the month was found
Say A1 is
Credit Interest November 2009
November is at the 17th character
So find is returning this array
{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,17,#VALUE!}
So the formula is now
=LOOKUP(2,1/ISNUMBER({#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,17,#VALUE!}),{"January","February",etc,"December"})
ISNUMBER returns a TRUE or FALSE response based on the test if each value is a number or not.
Errors are not numbers
so now your formula looks like this
=LOOKUP(2,1/{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE},{"January","February",etc,"December"})
When doing a mathematical operation on a True or False value like 1/FALSE or 1/TRUE, the TRUE/FALSE is converted to 1/0 1=True, 0=False
So it's now
=LOOKUP(2,1/{0,0,0,0,0,0,0,0,0,0,1,0},{"January","February",etc,"December"})
Now it's going to create an array of the result of 1 Devided by each number in the array.
1/1 = 1
1/0 = #DEV/0!
so now the formula is
=LOOKUP(2,{#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,#DEV/0!,1,#DEV/0!},{"January","February",etc,"December"})
Now lookup wants to find the closest match for 2 in that array.
The closest match is 1, which is in the 11th position of the array
So it returns the 11th postion of the 2nd array = November.

Hope that helped, and hasn't fried your brain too much...