Forum Discussion
Split data from one column without using text to column
Hi, I have some data(Invoice Nos) in a col A in a text format separated with"/", i want to split the same with using a formula (NOT TEXT TO COLUMN) coz if new data is added I'll need to go throug...
Hi Deepak,
This can be done by using Power Query also knows as (Get & Transform Data in Excel 2016), and if you don't have Excel 2016, then you have to download and install https://www.microsoft.com/en-us/download/details.aspx?id=39379.
Please watch this https://www.youtube.com/watch?v=0roS4OVfD9k to figure out how to do this in Power Query.
Also, you can depend on this formula instead:
=MID($A1,IF(COLUMNS($A$1:A1)>1,(COLUMNS($A$1:A1)-1&COLUMNS($A$1:A1)-1)+1,COLUMNS($A$1:A1)),10)
Copy it in cell B1 and then drag it to the right.
But it works only if the length of the invoice numbers in column A is always 10.
Hope that helps
Haytham