How to consolidate cells that have both texts and numbers?

Question

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/:&nbsp; 3- High 2- Medium 1- Low etc.&nbsp;In such cases what is the best way to consolidate without going ahead with text to column option? I have to do this across multiple workbooks as well.&nbsp;&nbsp;Thank you!! Appreciate all the help I could get.&nbsp;

harun24hr · Answer

eshsen&nbsp;You can go with TEXTSPLIT().&nbsp;=TEXTSPLIT(A1, "- ")&nbsp;Also can use FILTERXML().=TRANSPOSE(FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;SUBSTITUTE(A1,"-","&lt;/s&gt;&lt;s&gt;")&amp;"&lt;/s&gt;&lt;/t&gt;","//s"))&nbsp;&nbsp;

Forum Discussion

How to consolidate cells that have both texts and numbers?

1 Reply

Resources