up vote 0 down vote favorite
share [g+] share [fb]

I have used mid formula in excel 2003 to extract text from another excel cell. I now want to use the resultant value in a lookup formula - how do i get the lookup formula to use the Value derievd from the previous formula. at the moment it doesn't seem to be able to.

link|improve this question
It might help if you posted the two formulas in question. It's a bit unclear what you're trying to do at the moment. – mavnn Dec 10 '09 at 12:59
Show the formula for help – astander Dec 10 '09 at 13:02
feedback

migrated from stackoverflow.com Dec 10 '09 at 17:00

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 1 down vote

Mid extracts text, you must convert it to a number, try

=VALUE(MID(...))
link|improve this answer
dear all my current formula is =MID(A3,8,5) it extracts the text e.g IDA68 to cell B2 from the middle of text string which is a file number - that file number has an associated finacial value for example 20000. so i have a list on sheet 2 (=VLOOKUP(B2,Sheet2!$A$1:$B$27,2,FALSE) that is looking up the file number and posting the relevant file value against the relevant file reference – total newbie Dec 10 '09 at 13:15
feedback
up vote 0 down vote

Is the result a number? If so, use the Value function:

Value(Mid(A1, 3, 5))

link|improve this answer
dear all my current formula is =MID(A3,8,5) it extracts the text e.g IDA68 to cell B2 from the middle of text string which is a file number - that file number has an associated finacial value for example 20000. so i have a list on sheet 2 (=VLOOKUP(B2,Sheet2!$A$1:$B$27,2,FALSE) that is looking up the file number and posting the relevant file value against the relevant file number – total newbie Dec 10 '09 at 13:08
feedback
up vote 0 down vote

Hi guys i found out i was out by one characther on the mid formula and so was adding a space in front of the file reference so it actually is working now with no need for further adjustments - thanks for your time anyway....

link|improve this answer
feedback
up vote -1 down vote

If you're working with a lot of data, after doing the Mid calculations in one column, you could create a new column and do a paste special to paste only the values into the new column.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard