Hi All
I am looking for a formula which allows me to split a text string after the colon character ":" and return the RIGHT most characters (excluding the space after ":"). However, this is based on text from a dynamic array column which could grow / shrink in length and therefore, I want my formula to extract the word after the ":" to also be dynamic so it grows/shrinks in length dependent on column B.
I've used a fruit example to below to illustrate (obviously the real data is much longer and not fruit and veggies). I want a formula for Cell B2 which extracts just the fruit after the ":" from column A. The first two entries in A2:A3 don't have a ":" and I would like them to show as blank or "Blank". I've tried researching this myself and I found FILTERXML etc, but I couldn't get this to work. Any help, as always, would be much appreciated.
For your awareness the formula in cell A2 allows me to stack results from three other columns and is as follows (in the real data this is actually in cell I2 though, but I couldn't copy over my whole spreadsheet):
I am looking for a formula which allows me to split a text string after the colon character ":" and return the RIGHT most characters (excluding the space after ":"). However, this is based on text from a dynamic array column which could grow / shrink in length and therefore, I want my formula to extract the word after the ":" to also be dynamic so it grows/shrinks in length dependent on column B.
I've used a fruit example to below to illustrate (obviously the real data is much longer and not fruit and veggies). I want a formula for Cell B2 which extracts just the fruit after the ":" from column A. The first two entries in A2:A3 don't have a ":" and I would like them to show as blank or "Blank". I've tried researching this myself and I found FILTERXML etc, but I couldn't get this to work. Any help, as always, would be much appreciated.
| Book1 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Stacked Column - dynamic array | Split Text (but need this as a dynamic array if something is added below A14 | ||||||||
| 2 | *Individual Entry | |||||||||
| 3 | All Fruits and Veg | |||||||||
| 4 | All: Berries | Berries | ||||||||
| 5 | All: Root Veg | Root Veg | ||||||||
| 6 | All: Citrus | Citrus | ||||||||
| 7 | All: Apples | Apples | ||||||||
| 8 | All: Bananas | Bananas | ||||||||
| 9 | All: Pears | Pears | ||||||||
| 10 | All: Strawberries | Strawberries | ||||||||
| 11 | All: Blueberries | Blueberries | ||||||||
| 12 | All: Potatoes | Potatoes | ||||||||
| 13 | All: Carrots | Carrots | ||||||||
| 14 | All: Swede | Swede | ||||||||
| 15 | ||||||||||
Sheet1 | ||||||||||
For your awareness the formula in cell A2 allows me to stack results from three other columns and is as follows (in the real data this is actually in cell I2 though, but I couldn't copy over my whole spreadsheet):
Excel Formula:
=LET(range1,H2#,range2,DataValFruitSort,range3,B2#,rows1,ROWS(range1),rows2,ROWS(range2),rows3,ROWS(range3),cols1,COLUMNS(range1),rowindex,SEQUENCE(rows1+rows2+rows3),colindex,SEQUENCE(1,cols1),IF(rowindex<=rows1,INDEX(range1,rowindex,colindex),IF(rowindex<=(rows1+rows2),INDEX(range2,rowindex-rows1,colindex),INDEX(range3,rowindex-(rows1+rows2),colindex))))
