1

I have messy data in which different columns are all entered in the first column on Excel. I want to split the text to different columns, but am struggling with using the 'text to columns' function on Excel, as my columns are separated by multiple spaces (and the number of spaces is not always constant). Is there any way that excel can split the data into different columns based on a 'pattern'? This is how I want the data to look, ultimately.

Here is a link to my data.

Any tips welcome - thanks so much!

P.

|improve this question
  • 1
    would be great if you could post your example data in editable format, so we could replicate the problem. (you can edit it e.g. here: tablesgenerator.com/markdown_tables, or just paste it from Excel) – Máté Juhász Sep 11 '19 at 11:54
  • By hands. 1) Replace 3 spaces with 2 spaces a lot of times, until "0 replaces made" (or "search string not found") message; 2) Replace 2 spaces with some (one) char which does not occure in your values (@ for example for shown sample data); 3) Use Split data to columns - By separator, using char from (2) as separator. – Akina Sep 11 '19 at 12:01
  • @MátéJuhász Thanks very much. I have edited the original post to include a link to my data. As you can see, all the data is in the first column.I basically need 8 columns (like the 2nd link of my original post). I don't care about the data which doesn't have information on expenditure (such as row 7 - 15 of my data) so doesn't matter to me which columns they go in - I can manually delete those rows later. – pj_2106 Sep 11 '19 at 13:28
1

If it is only once, replace the spaces as suggested by @Akina.
If you plan to get more files with the same structure you could use the combination of MID and FIND functions to find the column limits.
Best of all - do the import from the original text file properly and you will not have to solve it. Even if it meant to save it as text file and re-import - this might be the easiest way. (see settings here)

|improve this answer
  • Thank you! Since I have a lot of files like this, I did what you suggested and imported it correctly from the text file, and used 'fixed width' while importing. – pj_2106 Sep 11 '19 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.