Forum Discussion
Shorten to certain characters and perform IF formula
Good morning/afternoon/evening all
I'm sure this is probably something simple I'm missing but I'm in a spot where I am trying to add a manufacturer indicator based upon the first three characters of data, such as what is below:
Basically, I'd like to remove the "Make" column. Currently I'm using a simple =LEFT(VINcell,3) to shorten it, and then =IF(MAKEcell="1GB","Chevrolet","GMC").
I'm probably missing something simple or it's not possible, but many thanks in advance!
In F2:
=IF(LEFT(D2, 3)="1GB", "Chevrolet", "GMC")
Alternatively:
Create a table elsewhere with 2 columns Make and Brand. Name the table - for example - Makes.
Add a row for each unique make:
The formula in F2 can then be =XLOOKUP(LEFT(D2, Makes[Make], Makes[Brand], "")
This makes it easy to add new makes in the future, if necessary.
2 Replies
- 19adam99Copper Contributor
Thank you!
Looking at it now, that makes it so much easier. I guess I was just struggling to wrap my head around the syntax. 😅
In F2:
=IF(LEFT(D2, 3)="1GB", "Chevrolet", "GMC")
Alternatively:
Create a table elsewhere with 2 columns Make and Brand. Name the table - for example - Makes.
Add a row for each unique make:
The formula in F2 can then be =XLOOKUP(LEFT(D2, Makes[Make], Makes[Brand], "")
This makes it easy to add new makes in the future, if necessary.