|What do you mean by "Excel didn't accept it like a formula"?|
Did it pop up the message that starts with "The formula you typed contains an error" or did it return an error value in the cell itself?
I can't test it in 2007 right now because I don't have that version on the machine I'm using at thsi time, but if I Copy-Paste the formula right from my post into 2003, it works as expected. There is nothing in the formula that shouldn't work in 2007, but I won't be able to test it until tonight.
That said, here is how the formula works:
Let's start by breaking down the 2 functions used:
MID(text, start_num, num_chars)
This function will look at the "text" in the text argument, which can be an actual text string ("Hello") or a reference to a cell (A1).
It will then start at the position specified by the start_num argument and return the number of characters specified by the num_chars argument.
Let's look at this example from your data:
1 Food 400 (27)
=MID(A1, 1, 3) will return Foo: 3 characters starting at character position 1
=MID(A1, 6 , 3) will return 400: 3 characters starting at character position 6
FIND(find_text, within_text, start_num)
find_text is the text string you are trying to find within the string designated by the within_text argument. As with the MID function, both of those arguments can be either an actual text string or a cell reference.
start_num is the character position within within_text where you want FIND to start looking.
e.g. =FIND("A", "123ABCA" ,1) will return 4 since A is in the 4th position if we start at position 1.
However, if we use a start_num of 5, it will return 7, since it will ignore the first A and find the last A which is in postion 7.
OK, so putting this all together...
Food 400 (27)
If I thought that Food would always be a 4 character string and that the value you want returned was always going to be a 3 character string, I could simply use this to return 400:
=MID(A1, 6, 3)
However, I am assuming that the length of the first string (Food) will vary as will the length of the value you want returned (400), therefore I have to determine the position of the first and last character of that value (e.g. 400).
That's where the FIND function comes into play.
When I look at the string, I see a space before the 400 and a space afterwards. So I use FIND to find the position of the 1st space and use it again to find the position of the 2nd space, I can determine the starting and ending position of the value (e.g. 400). Since I don't know what the 1st or last character of the value will be, but I do know that there is a space before and after it, I can FIND those spaces and use the values returned as the arguments for the MID function.
=FIND(" ", A1, FIND(" ", A1))
This says to find a space (" ") in A1, but start looking from the postion of the 1st space, so that it returns the position of the 2nd space.
So once we find the position of the 1st space and the position of the 2nd space we adjust the values returned for those postions to determine the exact length of the value between those spaces and use that as the num_chars argument for the MID function.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.