There was a way to do this manually too.
1. Copy HAR and it's components codes, and PasteSpecial>Transpose into new workbook. Like
1000639594 35415 86003
1000687858 36415 82785 83520 ...;
2. Save the new workbook as comma-delimited text file;
3. Open the text file with Excel without splitting (i.e. comma-delimited string remains in same cell);
4. Before the column with comma-delimited list, add 2 columns. Into 1st one, enter the formula to get HAR code from comma-delimited list (the leftmost one). In second one, use SUBSTITUTE() to get the comma-delimited list without HAR code (read from 1st column);
5. Copy columns with formulas, and overwrite them with PasteSpecial>Values. Delete column with original comma-delimited list. Save as Excel file.
It's done.
|