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
Best Answer
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:
Explanation:
iferror(join("|", filter(E4:H4, len(E4:H4))), "")
joins nonempty cells in the range E4:H4 into a regular expression such as7|abc|5H
\b(7|abc|5H)\b
, so that 7 does not get removed from 7H, for example.(?i)
, so that 2h is treated the same as 2H.regexreplace
replaces the above with empty strings4, 7, , 5H
then split-join will make it4, 7, 5H
.