text to columns on multiple columns

[chirning]

hello,

so I have a spreadsheet with approx. 600 columns and 38 rows. in every other column, I have a cell that needs text to columns with ( as the delimiter. I've tried to create a macro with the recording tool where I add in two blank columns and do text to columns on that section. but I dont want to have to perform this macro on every single section, as I'll be doing it about 300 times. Is there any way around this?

For reference, getting the top format from the bottom one, but for about 300 columns at the same time. Thank you so much for any help!

[jindon]

If this doesn't work, I need to see EXACT sheet layouts and EXACT result that you want.

Please Login or Register  to view this content.

[humdingaling]

Please Login or Register  to view this content.

increase i to 300
for my test i only did up to column P which is 16
screenupdating turned on for debugging...turn off in actual

[Solus Rankin]

Try:

Please Login or Register  to view this content.

[martindwilson]

wow the code gets longer and longer!

[chirning]

I appreciate the responses. I should have just uploaded this originally so be more clear. In file below, what I need to do is separate the title coding from each persons name cell without getting rid of the bio that follows. Using text-to-columns doesn't work cleanly because some cells have more than one "(" which the delimiter I used. For example, "John Smith (CEO)(Prior)" would need to become "John Smith" and "CEO Prior" in two seperate cells, not three.

Also I can't lose the bio info that follows each name, so replacing the content of those cells with the title codes is no good. Ideally, each company in this list would have three columns, name, title, and bio and then be followed by a column border to help make this visually appealing to those I'll present it to.

I appreciate any help you can offer-I'm in over my head here. Thank you so much.

EDIT-I have only uploaded a piece of the file, original is too large but it should give you an idea of the layout.

[jindon]

See if the result in Sheet2 is what you are after.

Please Login or Register  to view this content.

[chirning]

thank you thank you! looks good