Return fixed value based on the first letters

[mente73]

Hello Guys,

I would appreciate your help.

I have a list of multiple account numbers and I need to sort them brand meaning that if acct MB12345 should go under MyBooks, if account XB12345 goes under Xbooks brand

The problem I have is that some accounts start with the same first two letters

Example

MB12345
MBC12345

Any formula that would help me to accomplish this?

The report should look like this

A B
Account Brand
MB1234 MyBooks
MB78945 MyBooks
MB13579 MyBooks
MBC13579 MyBooksCasting
MBC13579 MyBooksCasting
USR3456 UnitedSelfReseler

Thank you in advance

[MarvinP]

Hi Mente,

See if the formulas in the yellow section work for you. The formulas in B2 and C2 can be pulled down to separate your text into letters and numbers.

Parse 2 or 3 letters from first of string.xlsx

[mente73]

Hello MarvinP,

Thank you for your assistance.

It worked but that is not what I was looking for.

I have attached a file with how the end result should look like.

Like I mentioned. I only have the column with the accounts (Column A) in column B I need a formula to return the name of the brand based on the first letters of the account number.

Any help will be much appreciated.

mente73

[jeffreybrown]

Are you looking for this? The best thing to do is build a lookup table.

Edit: Change the formula in B2 to >> =VLOOKUP(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)),$E$2:$F$5,2,0)

[MarvinP]

Hi Mente,

Here is another method, different than Jeff's above:

It is a little shorter formula at:

Please Login or Register  to view this content.

I built a lookup table that you would need to do and have the 3 letter at the top of the lookup table.

VLookup for Left 3 or 2.xlsx

[mente73]

Hello Jeffrreybrown and MarvinP,

Both options worked as expected, I really appreciate your assistance.

[jeffreybrown]

You are very welcome. Thanks for the feedback and rep.