I have a file of raw data coming out of an access database weekly. There are a number of columns which I need to run the "Text to Column" on. Is there a way to automate this? All of the headers needing to be converted have the % symbol in the header string.

So I need to do something like

if (column header contains a '%') then text to column for each column where the header is not blank.

Is this something that can be done?

Thanks ahead of time for any help, it is greatly appreciated!


  • If you text to column a column and the next column is already populated that data will be overwritten. If that's something that could happen here, I suspect this will be somewhat complicated (copy the column to another tab, text to column, count max columns used, insert that many in the original sheet at that columns original position, copy and paste from new tab to old tab, repeat for next column). Which is too broad to be answered here on SO. I would suggest recording a macro and learning some VBA with that starting point code.– JNevillFeb 13 at 17:20
  • JNevill I am highlighting the column hitting the text to column button and when the menu comes up just clicking finish. So there is no data being overwritten in any other columns it is all internal to the column selected.– JesseFeb 13 at 17:34
  • Can't you change the query so the split is handled there?– jkpieterseFeb 14 at 9:17

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.