1

I want to know if there is a way to add multiple numbers with letters to one cell and each number/ letter get eliminated when that number gets placed in another cell.

For example:

  • cell b4 has: 4, 7, 8, 5H
  • I put "5H" into any cell of E4:H4
  • resulting in 5H being removed from cell b4 leaving just 4, 7, 8

I would like to be able to do this with any one, any multiple, or all the numbers in cell 5H to be removed when their numbers are entered into any of the cells of e4:h4.

Here is a copy of the Spreadsheet https://docs.google.com/spreadsheets/d/11uvHy49n0i-Yj905wm4B2d_M9THHp16hS5jTrgYvPu8/edit?usp=sharing

Improve this question

1 Answer 1

Reset to default
1

One can't have both data and a formula in the same cell. If you want B4 to change in response to changes elsewhere (and don't want to write a script), then B4 must contain a formula. Then the data (such as "4, 7, 8, 5H") must be elsewhere. In my example, the data is in A4 but it could be any cell. Here is the formula for B4: 

=join(", ", split(regexreplace(A4, iferror("(?i)\b("&join("|", filter(E4:H4, len(E4:H4)))&")\b", ""), ""), ", "))

Explanation:

  1. iferror(join("|", filter(E4:H4, len(E4:H4))), "") joins nonempty cells in the range E4:H4 into a regular expression such as 7|abc|5H
  2. The regex is wrapped in word boundaries: \b(7|abc|5H)\b, so that 7 does not get removed from 7H, for example.
  3. It is made case-insensitive with the flag (?i), so that 2h is treated the same as 2H.
  4. regexreplace replaces the above with empty strings
  5. The split-join operation takes care of empty places left after replacement. For example, if regexreplace produced 4, 7, , 5H then split-join will make it 4, 7, 5H.
Improve this answer
9
  • Thank you for your response and I like what this formula. Is there a way to adjust it a bit? If I have both 2 and 2H added to E4:H4 then both “2s” go away leaving just the H ib B4. Is there a way to do this? Thanks once again for the answer you have given thus far. It has helped tremendously
    – David
    Commented Jan 20, 2016 at 15:25
  • Please clarify what was the input in your situation (the original content of B4), because apparently it's not the same as in the question.
    – user79865
    Commented Jan 20, 2016 at 16:41
  • Thank you so much for taking the time. Below is a link to how this page is set up utilizing your formula (love it). Patients are dropped into boxes e4:G10. The RN is assigned her rooms: Cell B2 (ie rooms 1 2 3 & hall bed 2H). When the nurse has a pt is room 2, the 2 disappears on bothe the room 2 and the hall 2 (2H). If I could make it so if a nurse's count could keep the H number with the H (for hall bed) then I could see what rooms were still open for that nurse. docs.google.com/spreadsheets/d/…
    – David
    Commented Jan 20, 2016 at 20:56
  • Right, I did not include word boundaries. Edited the answer now to include them.
    – user79865
    Commented Jan 20, 2016 at 21:49
  • I still can not get the 2H to go away when put into the cell but can get the rest to go away, if I put the room 2 in before the room 2H. if I put 2H before room 2 then both will go away
    – David
    Commented Jan 20, 2016 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.