Keep trailing spaces in fields after Text To Columns

New Contributor

Hello everyone! New to the forum. 


Let me say first I am doing this with macros recorded in Excel. 


I am taking a text file which includes spaces in each row, and placing in Column A. I am then using Text To Columns(TTC) to separate these rows into specific lengths. After allowing for some manual repairs of individual fields, I need to then use the CONCATENATE or '&' to glue these fields back together into one column. 


Unfortunately, no matter what I have tried including converting the columns to text in TTC will keep trailing spaces in these particular cells. We found a work around of sorts; convert all the spaces in the rows to a pipe '|', run the TTC and then convert the pipes back into ' ' spaces. This works great for most; but if a field is all numbers, even though formatted as text, the cell drops the spaces anyway. 


I would like a fix for all the cells as it would be nice to not use a workaround, but will be satisfied to get the number part fixed. I am more than happy to share a file with some test data and the macros. 


Thanks in advance,


Maury French

Dallas College

2 Replies
I don't know what that means either.
If I may ask, which part? And since you said either, I suspect someone else replied and removed their post. I realize now the paragraph about pipes is radically confusing. In a nutshell, for the rest of it, if I do a Text To Columns from a large string, and, say, characters 4-9 are 'AB ', I need those trailing blanks to be maintained in the cell. Same if the characters were '12 ' I need that five character total maintained.