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

10/11/21
Accepted

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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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([email protected], [Room ID]@row) - 1)

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • No more than 4 letters!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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([email protected], [Room ID]@row) - 1)

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.