Is there a way to pull specific characters from cells that have inconsistent data?

I have a weird situation where I need to pull specific data from cells that don't consistently look the same. What I need the sheet to do is this:

I have a column called "Room ID" that will have data that looks like one of the following: AB123 or ABC1234 and other variations.

On the same sheet I have a column called "Building" that I'd like to show just the data at the front of each cell (the letters) from the "Room ID" column. So from AB123 I'd want to show AB. From ABC1234 I'd like to show ABC.

In one more column on the same sheet called "Floor" I'd like to be able to pull just the first number that appears in the cell. So for any of the examples above it would just display 1.

Below is what I would like it to look like.

Any help coming up with a solution would be much appreciated!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Let's start with pulling the floor. This should work for you.

    =IFERROR(IFERROR(IFERROR(VALUE(MID([Room ID]@row, 2, 1)), VALUE(MID([Room ID]@row, 3, 1))), VALUE(MID([Room ID]@row, 4, 1))), VALUE(MID([Room ID]@row, 5, 1)))

    Then we can leverage this to find the stopping point of the letters.

    =LEFT([Room ID]@row, FIND(Floor@row, [Room ID]@row) - 1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!