Using MID w/ IF

Rembrandt7

New Member
Joined
Jan 18, 2017
Messages
3
I am trying to use mid with if, I tried nest IF, but that didn't work. I am using the following it work but will not give me the response I need.

=LOOKUP(MID(ROOM_NUMBER,2,1)+0,1,"1st Floor")


ROOM_NUMBERFLOOR
N1017

<tbody>
</tbody>
=LOOKUP(MID(ROOM_NUMBER,2,1)+0,1,"1st Floor")
N1047

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First, is ROOM_NUMBER a named range and second, what are you trying to do exactly?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,926
Office Version
  1. 365
Platform
  1. MacOS
you haven't told us what you want to output

Room_number - is that a named range
or the column

explain the data structure and what output you would like
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If it is a named range, this is my guess of what you want:
=MID(ROOM_NUMBER,2,1) & LOOKUP(MID(ROOM_NUMBER,2,1)+0,{1,2,3,4},{"st","nd","rd","th"}) & " Floor"

or a littler shorter:

=MID(ROOM_NUMBER,2,1) & CHOOSE(MIN(MID(ROOM_NUMBER,2,1),4),"st","nd","rd","th")&" Floor"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,266
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top