Extract Data

[Rubber Beaked Woodpecker]

In cell A1 I have the following data

14:152 Orange6 Blue7 Green10 Black

Is there a formula or code I could use that extracts only the data I require and insert it into the range A10:A13 as follows;

A10: Orange
A11: Blue
A12: Green
A13: Black

Many thanks

 

[JamesCanale]

This might work. It strips out anything that's not letters - except for a few characters - but that could be tidied up with an even longer formula. It should work in 2016.

MrExcelPlayground20.xlsx
	A
10	14:152 Orange6 Blue7 Green10 Black
11
12
13	Orange
14	Blue
15	Green
16	Black
Sheet28

Cell Formulas
Range	Formula
A13:A16	A13	=(FILTERXML("<t><s>"&SUBSTITUTE(TRIM(CONCAT(CHAR(IF((CODE(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1))<65)+(CODE(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1))>122),"32",CODE(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1))))))," ","</s><s>")&"</s></t>","//s"))
Dynamic array formulas.

 

[KRice]

Here is another way to do this with a formula (formula in A10)...not very elegant, but with an older version of Excel, your options are more limited. You could do this with VBA or a user-defined function. Alternatively, if you have access to Excel 365, some other functions are available to make this more convenient (formula in A3). In pre-Excel 365, the A10 formula will need to be entered with Ctrl-Shift-Enter to confirm it as an array function, and the formula pulled down through cell A13.

MrExcel_20240107 (version 1).xlsx
	A
1	14:152 Orange6 Blue7 Green10 Black
2
3	Orange
4	Blue
5	Green
6	Black
7
8
9
10	Orange
11	Blue
12	Green
13	Black
Sheet7

Cell Formulas
Range	Formula
A3:A6	A3	=LET(tt,TEXTAFTER(A1," "),TEXTSPLIT(TEXTJOIN("",TRUE,IF(ISERROR(MID(tt,SEQUENCE(LEN(tt)),1)*1),MID(tt,SEQUENCE(LEN(tt)),1),"")),," "))
A10:A13	A10	=FILTERXML( "<t><s>"& SUBSTITUTE( TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1, "0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),":","")), " ", "</s><s>" ) &"</s></t>", "//s" )
Dynamic array formulas.

 

[alansidman]

And yet one more solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Character Transition", each ([Column1.1] <> "14:")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.2"})
in
    #"Removed Columns"

Book4
	A
1	Column1
2	14:152 Orange6 Blue7 Green10 Black
3
4
5
6
7
8
9
10	Column1.1
11	Orange
12	Blue
13	Green
14	Black
Sheet1