Forum Discussion

CoriatAMCS's avatar
CoriatAMCS
Copper Contributor
Dec 19, 2023

Using Left() in a Vlookup returns the number of characters in my Left() statement

THE FORMULA IS HERE:

=IF(LEFT(VLOOKUP(LEFT([@[Company (actual)]],9),LEFT('[Recurring BI Classic Customers 12-7-23.xlsx]TRUX'!$A:$D,9),4,0),4)="TRUX",VLOOKUP(LEFT([@[Company (actual)]],9),LEFT('[Recurring BI Classic Customers 12-7-23.xlsx]TRUX'!$A:$E,9),5,0),"NONE")

It works fine

EXCEPT
It returns only the first 9 characters of the resulting column data

excel

2 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 19, 2023

    CoriatAMCS 

    It seems like you are using the LEFT function on the result of your VLOOKUP function, and you're encountering an issue where it only returns the first 9 characters. The issue might be related to the formatting or data type of the result.

    To ensure that the result of your VLOOKUP is treated as text and retains its formatting, you can use the TEXT function to explicitly convert the result to text. Here's an updated version of your formula:

    =IF(LEFT(TEXT(VLOOKUP(LEFT([@[Company (actual)]],9),'[Recurring BI Classic Customers 12-7-23.xlsx]TRUX'!$A:$D,4,FALSE), "0"),4)="TRUX", TEXT(VLOOKUP(LEFT([@[Company (actual)]],9),'[Recurring BI Classic Customers 12-7-23.xlsx]TRUX'!$A:$E,5,FALSE), "0"), "NONE")

     

    In this modified formula, the TEXT function is applied to the result of the VLOOKUP function to ensure that it is treated as text. The second argument of the TEXT function specifies the format, and "0" is a general format.

    This should help you retain the entire content of the result from your VLOOKUP without being limited to the first 9 characters. Adjust the formula based on your specific requirements and the structure of your data. The text, steps and formulas was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources