Vlookup & Trim functions for a cell with text & number

[Dunnerz]

Hi all,

Need a bit of help please.

I'm have raw data which is dumped into Column C, which contains text and numbers, but is in text format i.e. 52345 Fixed Assets xxxx etc.

I have used the following formula to pull the numbers I want into Column A: =IF(COUNT(FIND({5,6,7,8,9},C29))>0,LEFT(C29,6),"")
The numbers I want only start with those numbers above.

In column B, I am using a Trim function as some numbers are only 5 digits therefore they'll have a space. Numbers will not be longer then 6 hence the Left(C29,6) formula above.

Problem is some of the numbers begin with the letter 'C', therefore when I run: =IF(A12="","",VALUE(TRIM(A12))) I get an error on the values with a C in front, but obviously it works for cells with an actual Value and no letter. Is there a way around this to include number values and text?

I want to then use this column as the table_array for a Vlookup in another worksheet, within the same workbook.

The reference I'll use, is also system data, which will contain the 5 digit numbers and the 6 digit numbers with the letter C in front.
i.e.
52345
C52346
C93394
64452 etc.

I tried to get around this by running a Vlookup directly off the IF(Count(Find) function that I pasted above (Column A). I thought I could lookup text and numbers using:
=IF(A12="","",IFERROR(VLOOKUP(IF(ISERROR(A12),A12,A12+0),'SAP Data (ZFI_GJ87)'!$C:$C,1,FALSE),"Acct NOT listed"))

This works for the numbers, but won't lookup values with the letter C in front.

I'm caught between the two.

Is there a way to do this or do I have to run formula's in two separate columns and the manually combine them? Hoping to automate this process as much as possible, without VBA?

Many Thanks.

 

[CharlesChuckieCharles]

First bit why not

=IF(COUNT(FIND({5,6,7,8,9,"C5","C6","C7","C8","C9"},LEFT(C29,2)))>0,TRIM(LEFT(C29,6)),"")

Not sure its all that important to have the numeric code as numeric to the process

using the text representation of a number to lookup the Value representation of the number shouldn't cause any problems


=IF(A12="","",IF(ISERROR(VLOOKUP(A12,'SAP Data (ZFI_GJ87)'!$C:$C,1,FALSE),"Acct NOT listed"))

 

[Dunnerz]

Thanks so much for your help! You set me on the right path, I just had to make a few tweaks to the data and the formula you listed above to make it work.

For reference, here was the complete solution:

I removed the Trim column and combined the formula as you suggested: =IF(COUNT(FIND({5,6,7,8,9,"C5","C6","C7","C8","C9"},LEFT(C29,2)))>0,TRIM(LEFT(C29,6)),"")

I thought that the numeric and text in the same formula would cause issues, but that was an error in my logic, so this worked perfectly and saved space.

This formula was causing me issues: =IF(A12="","",IF(ISERROR(VLOOKUP(A12,'SAP Data (ZFI_GJ87)'!$C:$C,1,FALSE),"Acct NOT listed"))

Initially I thought it was because there was a bracket missing to secure the ISERROR function. But I was still getting "Acct NOT listed" even though I knew it was there.

I went in to the raw data and I created a new column and used the TRIM function. I hadn't realized this was an issue, as when I used the LEN function, it showed the correct amount of digits.

I was still getting an issue though. A standard Vlookup worked to pull the data, but the whole formula would return "FALSE". I changed the ISERROR to an IFERROR and everything now works.
=IF($A12="","",IFERROR(VLOOKUP($A12,'SAP Data (ZFI_GJ87)'!$S:$S,1,FALSE),"Acct NOT in SAP"))

Thanks again for your help.