How can I ensure entries in a column are 16 chars long using spaces?

**compact** · 10-18-2007, 03:38 AM

Hi

Sorry for the confusing title.
I'm not too sure what this will be called, so struggled explaining it, let alone looking for it

I have a column with text in it. I need to set the length of each entry to say 16 chars, using only spaces after the word, to make up any missing.

There are several columns I need like this, which I will then export out to a flat txt file to feed into another program.

i.e.
"qwerty"
"supercool"
needs to be

"qwerty "
"supercool "

Any ideas?

i have tried a few basic ideas, but when you copy them across you loose the spacing

**VBA Noob** · 10-18-2007, 03:43 AM

Say your data in Col A starting at A1 then enter this in B1 and drag down

=A1&REPT(" ",16-LEN(A1))

Paste special values and then delete Col A

VBA Noob

**compact** · 10-18-2007, 05:49 AM

cheers
(I only needed 15 not 16 chars)
That does it, except for if you copy and paste more than one column into a notepad/textpad, it increase the number of spaces by 1, in all but the last coumn

So you have to modify the forumlar so it reads

Column 1 Column 2 Column 3
=A1&REPT(" ",14-LEN(A1)) =B1&REPT(" ",14-LEN(B1)) =C1&REPT(" ",15-LEN(C1))

So that gives the correct chars in each column

Also even with this change to correct it, the space between column 1 and column 2 is large than a normal space, rather a double space.

**compact** · 10-18-2007, 06:11 AM

Ok
I have played a bit and now it works like this.

=A1&REPT(" ",15-LEN(A1)) & B1&REPT(" ",15-LEN(B1)) & C1&REPT(" ",15-LEN(C1))

So you get it all in one cell.
Then copy and paste special Value onto 2nd sheet, then save to txt file.

Cheers guys, seems to be working

How can I ensure entries in a column are 16 chars long using spaces?

LinkBack

Thread Tools

Rate This Thread

Display

How can I ensure entries in a column are 16 chars long using spaces?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions