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

Jenna Simpson

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!

Paul Newcome

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)

Paul Newcome

What is the maximum number of letters that could be at the beginning?

Jenna Simpson

No more than 4 letters!

Paul Newcome

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)

Jenna Simpson

This worked perfectly! Thank you so much @Paul Newcome!!

Paul Newcome

Happy to help. 👍️