Text To Column For Every Column On Sheet

[JADownie]

Hello -

I noticed today that I was unable to do a tab delimited text to column now for multiple columns at the same time - Ex. selecting columns A:C and selecting Text to Column. Is there possibly a way with VBA to have a macro which could determine how many columns were on sheet, and for each, individually perform a text to column? I have a file now with around 500 columns, and was hoping that I could avoid having to manually do this for each. Thanks for any guidance or assistance with this today.

 

[Jared_8878]

If you have columns a and b (let alone the 500 you mention) and do a text to column on both, the column a's contents are going to overwrite b's. That will only be exaggerated with your 500.
So you would have to take column a and to a text to column with its information on a new sheet, then measure how many columns that went, then start with column b's data (from the original sheet) and expand that from the next unused column on the new sheet and so on.

 

[baitmaster]

The only way I can see this working is to copy each column to a new temporary worksheet, perform text to columns on each, and at the end of the routine, copy all results back into one sheet, then deleting all the temporary worksheets

You can identify the actual number of columns you are looking at by using lastColumn = sheets("your sheet name").Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).column

 

[JADownie]

Thanks for the help! I actually have 831 columns of data in my sheet now. And I need to do a text to column on each to convert them all into #s. Unfortunately when I go to change the cell format to #s, when I pivot and sum my values, it still does not sum anything. But I noticed that as soon as I did a text to column on the column of data, and then refreshed my pivots, the sums worked correctly. So when I am wanting to do a text to column now for all 831, I am not actually wanting to move text from one column to another, but rather just run the text to column function on each in my sheet. Can that be done, or is there a better/faster way for me to do this now? Thanks!

 

[baitmaster]

So you're doing this only as a workaround to another as yet unknown issue? In which case you need to work out the problem and find a better fix

What are you using as a delimiter? Perhaps getting rid of that character fixes the problem, you could try find & replace on it instead

 

[JADownie]

Yes exactly, I was hoping to find a work around to convert my data. I was manually and individually doing tab delimited text to column (nothing moves from cell to cell) and that seemed to convert my #s to sum in my pivots, but I didn't know if there was a better way to do this now....

 

[baitmaster]

interesting. I've just tried that with some random data, where I happened to have numbers and commas, formatted as text. The impact was to convert them all (where possible) to numbers from text strings - is this happening for you?

If so, there are two things to watch for
1) are you using numbers that are being converted from text to numbers? If so, copy the number zero, and paste special it onto all your data, using VALUE and ADD parameters
2) if any number is not correctly formatted within a string (e.g. only 2 digits after a comma) then it will not convert. Check this has happened for all numbers - cell alignment will be to the left for a text string, and right for a number

 

[JADownie]

Basically my file is 800 columns with headers and 1s (indicating a Y) and 0s (indicating a N). So I don't have long strings of numbers. Let me see if the add function works now, I have never used that one. Thanks!

 

[baitmaster]

If your data is formatted as strings you will get this problem, its a common issue when importing data from certain types of data source, e.g. SAP. You may also need to check the cells are not formatted as text, and change these before adding 0, if necessary