Get different numbers to return text??

[Curalice]

In Col A, I have a list of 3 digit numbers eg:
110
111
112
113
115
116
120
Some numbers begin with 1, some with 2, 3 or 4.

I have two formula problems:

In Col B I want a formula to return "case 1, if the number begins with
1, "case 2" if 2, etc.
I tried variations of this formula but it I am missing something.
=IF((LEFT(A1)="4","case 4",""),(LEFT(A1)="3","case
3",""),(LEFT(A1)="2","case 2",""),(LEFT(A1)="1","case 1",""))

In Col C I want a similar result, except that "case 5", "case 6" etc.
would be correct for a mix of numbers, eg "case 5" would be the answer
for, say, any of 110 trrough 115, 315, 330, 410 or 411.

I hope that is clear. I am sure this is simple, but I am confused and
appreciate the help.
Thanks.

 

[Guest]

in column B use

="case " & left(A1,1)

I cannot see the logic behind the results you describe for column C.
Perhaps you can clarify that a little - or a lot.

 

[Ron Coderre]

Try this:

For a number in A1
B1: ="Case "&LEFT(A1,1)

Does that help?

Regards,
Ron

 

[vezerid]

Curalice,
First of all there is a simpler way for the Col B formula:
="Case " & LEFT(A1,1)

But if you try to do it with IF, you will need nested IF's and not the
syntax you have:
=IF(LEFT(A1,1)="4", "CASE 4", IF(LEFT(A1,1)="3", "CASE 3",
IF(LEFT(A1,1)="2", "CASE 2", IF(LEFT(A1,1)="1", "CASE 1", "UNKNOWN
CASE"))))

For Col C I suggest a 2-column table, where the first column will have
the various numbers under consideration and second column will have the
case. You can then use VLOOKUP(). Assuming the table is in G1:H30
=VLOOKUP(A1, $G$1:$H$30, 2, FALSE).

You are not so clear re your Col C values so there might be a more
efficient way, but this one will certainly work.

HTH
Kostis Vezerides

 

[Guest]

For Column B:

="case " & LEFT(A1,1)

For Column C:

=IF(OR(A1={110,111,112,113,114,115,315,330,410,411}),"case 5",IF(OR(A1={your
numbers here}),"case 6","case 7"))

Not sure if that's quite what you're going for, but maybe will point you in
the right direction.

HTH,
Elkar

 

[SteveG]

For column c, I think I know what you are looking for. Try this.


=IF(OR(LEFT(A1,3)*1={110,111,112,113,114,115,315,330,410,411}),"case
5","")

One of your issues is that the LEFT function will return the 110 as
text so I multiplied it by 1 to turn it into a numeric value.
Otherwise, your numbers in the {} would need to have quotations around
each.

This way you can use another IF statement to accomodate for case 6 and
not run into the problem of too many nested statments.

Does that help?

Steve

 

[Curalice]

Thanks, guys!
The Col B solution from all worked fine.
The Col C solution from Elkar did the trick.