Forum Discussion

Maury57's avatar
Maury57
Copper Contributor
Mar 29, 2021

Keep trailing spaces in fields after Text To Columns

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

Excel
Macros and VBA
    • Maury57's avatar
      Maury57
      Copper Contributor
      Mar 30, 2021
      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.

Resources