[ad_1]
all. I’m currently running into some difficulties with splitting and regular expressions in a Google spreadsheet. I’m attempting to split the contents of a cell across a row, but only pulling out sequences of four consecutive digits (representing years) and only using cell formulas (not functions). Eventually, this formula would apply to an entire column, but I’ve limited it to a single cell for the time being. For example, given a cell “I2” with the contents:
2009; Library of Congress; 1939-1945; 23rd 1984; 16
I need a result (placed in “J2, K2, L2, M2, etc.”) like:
2009 1939 1945 1984
This sample cell is as representative as I’m aware of for various possibilities that are likely to come up, though the number of entries between semicolons varies from one to many. In my own attempts so far, I’ve ended up with two formulas that are close to what I need, but both fall short.
1) The first formula is:
=ArrayFormula(SPLIT(SUBSTITUTE(REGEXREPLACE(I$2, "[^d-;]", ""),"-", ";"), ";"))
which achieves (in "J2, K2, L2, M2, N2"):
2009 1939 1945 231984 16
2) The second formula is:
=ArrayFormula(SPLIT(SUBSTITUTE(REGEXREPLACE(REGEXREPLACE(I$2, "[^d]", ";"), "[^d-;]", ""),"-", ";"), ";"))
which gets me (in "J2, K2, L2, M2, N2, O2"):
2009 1939 1945 23 1984 16
I’ve been trying to think of a way to limit the formula’s returns with "d4", for example, but no combination or alterations I’ve made so far have been successful. Does anyone have any insight which would solve this problem?
[ad_2]
لینک منبع