Forum Discussion

eshsen's avatar
eshsen
Copper Contributor
Sep 21, 2022

How to consolidate cells that have both texts and numbers?

Hi All, I'm new to excel and taking a course which required me to consolidate data in 4 workbooks. The cells that need to be consolidated have both texts and numbers in it. Eg/:  3- High 2- Medium 1...
excel
Formulas and Functions
Harun24HR's avatar
Harun24HR
Bronze Contributor
Sep 21, 2022

eshsen You can go with TEXTSPLIT().

 

=TEXTSPLIT(A1, "- ")

 

Also can use FILTERXML().

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,"-","</s><s>")&"</s></t>","//s"))

 

 

Resources