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

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