Return value when word begins inbetween two letters

**se15** · 06-14-2013, 06:27 AM

Hi,

I have a list of accounts in column B. I want to add attach a value to column A based on the letter column B begins with. So:

Accounts beginning with all letters between A & F I want column A to return "Bob".
Accounts beginning with all letters between G & O I want column A to return "Tom".
Accounts beginning with all letters between P & Z I want column A to return "Harry".

I also want accounts beginning with numbers, to say "Bob" as well

What is the best way of doing this?

Thanks

**benishiryo** · 06-14-2013, 06:40 AM

hi se15, welcome to the forum. try:
=LOOKUP(CODE(UPPER(LEFT(B1))),{48,71,80},{"Bob","Tom","Harry"})

each numbers & alphabets have a code number, so 48-70 is actually 0-9 & from A-F, 71-79 is G-O, and so on

**martindwilson** · 06-14-2013, 06:43 AM

try
=IF(OR(LEFT(B1)={"g","o"}),"tom",IF(OR(LEFT(B1)={"p","z"}),"harry","bob"))

**se15** · 06-14-2013, 07:08 AM

That's great, thanks guy, works perfectly

**Ghozi Alkatiri** · 06-14-2013, 08:12 AM

=INDEX({"Bob","Tom","Harry"},MATCH(LEFT(B1),{"A","G","P"}))

Return value when word begins inbetween two letters

LinkBack

Thread Tools

Rate This Thread

Display

Return value when word begins inbetween two letters

Re: Return value when word begins inbetween two letters

Re: Return value when word begins inbetween two letters

Re: Return value when word begins inbetween two letters

Re: Return value when word begins inbetween two letters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions