Mac: Text to columns not working for me (I only need one column split into two).

[karma002]

Hello,

This is my first question post. I have attached a sample sheet with six terms and definitions copied and pasted from an open source government website into one column. I need the one column split into two - you will see the headings I added: LABEL/TERM and DEFINITION. I believe I have exhausted the functions under 'Data-text to columns' and cannot find a solution with this particular copied and pasted data. Also, I do not need to keep the hyperlinks on the labels/terms. Actually, it's better to get rid of them if that makes it any easier.

Thank you so much for any help. This will be a huge data set when completed, but I obviously need the formatting issue solved first!

Best,
Ryan

[Roel Jongman]

you do not need text to columns.

always look for paterns. In this case the patern is that the defintion is 2 lines below the label.

so if you put "=A4" in cell B2 hen the definition will show behind the label
then copy down the formula to the relevant rows
and when all definitions are behind the label you simply copy whole column B and paste as text
then the definition is behind the label and you can remove the blank and unneeded lines.

[karma002]

Thank you so much. The first part worked great - adding "=A4" and so on. What's happening now is that when I delete the definition from the first column, it also disappears in the second column with a code that reads "=/REF!" - I think I missed something when you stated "then copy down the formula to the relevant rows and when all definitions are behind the label you simply copy whole column B and paste as text then the definition is behind the label and you can remove the blank and unneeded lines." Sorry - got confused with that.

[karma002]

Just making it clear that I am still having trouble with this. Any further help from anybody is appreciated.

[AliGW]

There will be more efficent code, I'm sure, but this will do it in PowerQuery:

Please Login or Register  to view this content.

[karma002]

Okay I'm getting closer. Thank you for this. So now if I copy the next seven terms and definitions from the website for example, where/how exactly would I insert them into your new version? I'm hoping they can now separate on their own into the two columns.
Thank you again.

[AliGW]

Assuming you are working in my copy of the workbook, just go to the Data ribbon and click on Refresh All. The results table should then update.

[karma002]

It's not updating for me. I do get a security warning about outside connections when I open your copy. I clicked "enable content." I attached your copy here with the letter 'B' terms pasted into my sheet. When I go to your copy and Refresh All, nothing happens.

I really appreciate your patience and time! I feel like I'm just missing something simple that you've already done.

[AliGW]

Ah - I have just spotted that you have the Mac version of Excel, not Windows, so sorry, it won't work for you. That's my fault for not reading your profile carefully enough - sorry.

[karma002]

Okay. Yes, that's correct. Mac version. Please let me know if you have leads and/or suggestions for further help on this. But thanks for everything!

[AliGW]

Thread moved to the correct area of the forum.