Return Numeric Value After Certain Text String

[CMJ]

Hi,

I have a sheet with a lot of rows of data such as:

[TABLE="width: 544"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hotel > Level 3 > Room 360 > Courtyard K 4 > Work-Complete[/TD]
[/TR]
[TR]
[TD]Hotel > Level 3 > Room 360 > Courtyard K 4 > Work-Complete[/TD]
[/TR]
[TR]
[TD]Hotel > Level 3 > Room 358 > Courtyard K 4A-HI.C > Work-Complete[/TD]
[/TR]
[TR]
[TD]Hotel > Level 3 > Room 354 > Courtyard K 10 > Work-Complete[/TD]
[/TR]
[TR]
[TD]Hotel > Level 3 > Room 349 > Courtyard K 5.S > Work-Complete

I need to identify the room numbers in a separate cell, i.e. after the work "Room". I only want to return the numeric value. Room numbers are 3 or 4 digits. Anyone have any good ideas?

Thanks!


[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

=MID(A1,FIND("Room",A1)+5,4)

 

[CMJ]

Hi,

=MID(A1,FIND("Room",A1)+5,4)

Lifesaver! Thanks for the help!

 

[jtakw]

Hi,

I would convert the results to Real numbers incase you need to do further comparisons/math with the extracted room number.
Just add +0 to the above formula like:

=MID(A1,FIND("Room",A1)+5,4)+0