How to find the fourth digit and return a specific text

[MMyslenski]

Hi all,

Someone showed me this at my last job but I can't remember it. I am trying to write a formula that will look at the fourth digit of a number, and then based on what that number is, return a specific text. As an example, if I had the following cells
A B
1 1234 Bill
2 4321 Bob
3 6789 Sally
4 9786 Sam

the formula would say "If in cell X, the fourth digit is 4, type Bill, if its 1, type Bob, if its 9, type Sally, if its 6, type Sam" I hope that makes sense. I searched this site for the last 15 minutes and found similar formulas but not exactly what I am looking for.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SWITCH(MID(A2,4,1)+0,1,"Bob",4,"Bill",6,"Sam",9,"Sally","")

 

[MMyslenski]

Hi & welcome to MrExcel.
How about

=SWITCH(MID(A2,4,1)+0,1,"Bob",4,"Bill",6,"Sam",9,"Sally","")

That worked!

I have a follow up question; is there a way to check multiple columns for input? as an example:

1234​			Bill
	4321​		Bob
6789​			Sally
		9786​	Sam

So if column A has data, do the look up, but if column A has no data, look in column B for data, if theres no data there, check column C. Is it possible to do multiple column look ups?

 

[Fluff]

How about

=LET(CL,IF(A2<>"",A2,IF(B2<>"",B2,C2)),SWITCH(MID(CL,4,1)+0,1,"Bob",4,"Bill",6,"Sam",9,"Sally",""))

 

[MMyslenski]

How about

=LET(CL,IF(A2<>"",A2,IF(B2<>"",B2,C2)),SWITCH(MID(CL,4,1)+0,1,"Bob",4,"Bill",6,"Sam",9,"Sally",""))

Amazing! This is the greatest website ever, and you are a top notch, wonderful human being. This was so helpful

IF you have the time, because I like to learn, if you could explain in small detail (so I can maybe figure it out on my own in the future) what each step of the function is doing, I would be extremely appreciative. If that's too much, at least I have the formula and can use it.

Thank you again. You've saved hundreds of hours in manual labor someone was doing before.

 

[Fluff]

This part is storing the value of A2, B2 or C2 in the variable CL IF(A2<>"",A2,IF(B2<>"",B2,C2)) then then this gets the 4th character & converts from text to a number MID(CL,4,1)+0 and the Switch function then switches the values.