Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to get an Excel function to recognise text
Hi,
Using worksheet named "Sheet1", to convert text which looks like this - "1001001 Sundry Expenses" into this "1001001" I'm using the following formula: =IF(B17="","",(IF(TYPE(VALUE(LEFT(TRIM(B17),1)))=1 ,LEFT(TRIM(B17),FIND(" ",TRIM(B17),1)),TRIM(B17)))) The original text is in Cell Bx and the result is in Ax In other worksheets I'm using Vlookups against the contents of column A to return the relevant columnar results. My problem is in the Vlookup formula if I enter something like =Vlookup("1001001",Sheet1!$A$Z,16,0) I get an #N/A. It seems I need to use the above trim formula on the sheet I'm performing the VLookup then use a Cell reference in order for the result to be returned i.e. Place the full account code and Desc (e.g. 1001001 Sundry Expences) somewhen in the new worksheet then use the formula mentioned above to trim this down to 1001001 then use the cell reference of the result in the VLookup (=Vlookup(C22,Sheet1!$A$Z,16,0)). How can I get Excel VLookup to recognise when I manually input a number (like "1001001") and to return the result if this number appears in Row A. Any help appreciated ...pc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to get an Excel function to recognise text
If you try to match text against real numbers, you'll never get a match.
And =Left() and =trim() all return text strings. If you need to match on numbers, maybe something like: =vlookup(--(LEFT(A1,FIND(" ",A1,1)-1)), .... The right minus sign (in --(left(...)) coerces the text "1234" to -1234 and the left minus converts it back to a positive number. PMC1 wrote: Hi, Using worksheet named "Sheet1", to convert text which looks like this - "1001001 Sundry Expenses" into this "1001001" I'm using the following formula: =IF(B17="","",(IF(TYPE(VALUE(LEFT(TRIM(B17),1)))=1 ,LEFT(TRIM(B17),FIND(" ",TRIM(B17),1)),TRIM(B17)))) The original text is in Cell Bx and the result is in Ax In other worksheets I'm using Vlookups against the contents of column A to return the relevant columnar results. My problem is in the Vlookup formula if I enter something like =Vlookup("1001001",Sheet1!$A$Z,16,0) I get an #N/A. It seems I need to use the above trim formula on the sheet I'm performing the VLookup then use a Cell reference in order for the result to be returned i.e. Place the full account code and Desc (e.g. 1001001 Sundry Expences) somewhen in the new worksheet then use the formula mentioned above to trim this down to 1001001 then use the cell reference of the result in the VLookup (=Vlookup(C22,Sheet1!$A$Z,16,0)). How can I get Excel VLookup to recognise when I manually input a number (like "1001001") and to return the result if this number appears in Row A. Any help appreciated ..pc -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 versus 2003 and the TEXT Function | Excel Worksheet Functions | |||
Can an Excel logic function change the color of a cell or text? | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
in excel how to display results of a function and text in the sam. | Excel Worksheet Functions |