Keep trailing spaces in fields after Text To Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2243359%22%20slang%3D%22en-US%22%3EKeep%20trailing%20spaces%20in%20fields%20after%20Text%20To%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2243359%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%20New%20to%20the%20forum.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20say%20first%20I%20am%20doing%20this%20with%20macros%20recorded%20in%20Excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20taking%20a%20text%20file%20which%20includes%20spaces%20in%20each%20row%2C%20and%20placing%20in%20Column%20A.%20I%20am%20then%20using%20Text%20To%20Columns(TTC)%20to%20separate%20these%20rows%20into%20specific%20lengths.%20After%20allowing%20for%20some%20manual%20repairs%20of%20individual%20fields%2C%20I%20need%20to%20then%20use%20the%20CONCATENATE%20or%20'%26amp%3B'%20to%20glue%20these%20fields%20back%20together%20into%20one%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20no%20matter%20what%20I%20have%20tried%20including%20converting%20the%20columns%20to%20text%20in%20TTC%20will%20keep%20trailing%20spaces%20in%20these%20particular%20cells.%20We%20found%20a%20work%20around%20of%20sorts%3B%20convert%20all%20the%20spaces%20in%20the%20rows%20to%20a%20pipe%20'%7C'%2C%20run%20the%20TTC%20and%20then%20convert%20the%20pipes%20back%20into%20'%20'%20spaces.%20This%20works%20great%20for%20most%3B%20but%20if%20a%20field%20is%20all%20numbers%2C%20even%20though%20formatted%20as%20text%2C%20the%20cell%20drops%20the%20spaces%20anyway.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20a%20fix%20for%20all%20the%20cells%20as%20it%20would%20be%20nice%20to%20not%20use%20a%20workaround%2C%20but%20will%20be%20satisfied%20to%20get%20the%20number%20part%20fixed.%20I%20am%20more%20than%20happy%20to%20share%20a%20file%20with%20some%20test%20data%20and%20the%20macros.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaury%20French%3C%2FP%3E%3CP%3EDallas%20College%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2243359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2243556%22%20slang%3D%22en-US%22%3ERE%3A%20Keep%20trailing%20spaces%20in%20fields%20after%20Text%20To%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2243556%22%20slang%3D%22en-US%22%3EI%20don't%20know%20what%20that%20means%20either.%3C%2FLINGO-BODY%3E
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.