Exclude Certain text from a cell.

%3CLINGO-SUB%20id%3D%22lingo-sub-1500885%22%20slang%3D%22en-US%22%3EExclude%20Certain%20text%20from%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Fpostpage%2Fboard-id%2FExcelGeneral%23%22%20target%3D%22_blank%22%3EFormulas%20and%20Functions%3C%2FA%3E%26nbsp%3B-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20below%20image%2C%20I%20know%20the%20values%20which%20are%20A%2C%20B%2C%20C%2C%20...H%20but%20I%20wont%20be%20having%20the%20values%20for%201%202%203%20..%20n%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20i%20capture%20the%20random%20values%20out%20of%20the%20fixed%20in%20a%20cell%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENOTE%3A%20All%20the%20variable%20is%20a%20%22text%22%20and%20the%20order%20of%20A%2C%20B%2C%201%20doesn't%20remain%20the%20same.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20function%20to%20capture%20the%20random%20value%20will%20help%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22paramdave_0-1593593169999.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202509iDCF5125639A11546%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22paramdave_0-1593593169999.png%22%20alt%3D%22paramdave_0-1593593169999.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1500885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502137%22%20slang%3D%22en-US%22%3ERe%3A%20Exclude%20Certain%20text%20from%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F715256%22%20target%3D%22_blank%22%3E%40paramdave%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORT(FILTER(%0A%20%20%20FILTERXML(%22%3CR%3E%3CN%3E%22%20%26amp%3B%20SUBSTITUTE(TEXTJOIN(%22%2C%22%2C%2CA%3AA)%2C%22%2C%22%2C%22%3C%2FN%3E%3CN%3E%22)%20%26amp%3B%20%22%3C%2FN%3E%3C%2FR%3E%22%2C%20%20%22%2F%2Fn%22)%2C%0A%20%20%20ISTEXT(FILTERXML(%22%3CR%3E%3CN%3E%22%20%26amp%3B%20SUBSTITUTE(TEXTJOIN(%22%2C%22%2C%2CA%3AA)%2C%22%2C%22%2C%22%3C%2FN%3E%3CN%3E%22)%20%26amp%3B%20%22%3C%2FN%3E%3C%2FR%3E%22%2C%20%20%22%2F%2Fn%22))%0A))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20379px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202708iFB82356CEBA57435%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Formulas and Functions - 

 

In the below image, I know the values which are A, B, C, ...H but I wont be having the values for 1 2 3 .. n 

How can i capture the random values out of the fixed in a cell? 

 

NOTE: All the variable is a "text" and the order of A, B, 1 doesn't remain the same. 

 

Any function to capture the random value will help

paramdave_0-1593593169999.png

 

1 Reply

@paramdave 

That could be

=SORT(FILTER(
   FILTERXML("<r><n>" & SUBSTITUTE(TEXTJOIN(",",,A:A),",","</n><n>") & "</n></r>",  "//n"),
   ISTEXT(FILTERXML("<r><n>" & SUBSTITUTE(TEXTJOIN(",",,A:A),",","</n><n>") & "</n></r>",  "//n"))
))

for

image.png