I am concatenating last and first names into a single cell but need to
have the phantom columns line up. I know the longest last name is 17
characters long so I need a macro which will add the appropriate number
of spaces to the right of each last name. Something like
-edit cell
-calculate length
-add appropriate number of spaces to right of last character
-end edit
-next cell
Any help would be great. Thanks

using functions not vb
if names in column a
formula in b1
=LEFT(A1&" ",17)
this adds 17 spaces to the name, then returns the left 17 characters of
this new string.

Works great,thanks

philcud wrote:
using functions not vb
if names in column a
formula in b1
=LEFT(A1&" ",17)
this adds 17 spaces to the name, then returns the left 17 characters of
this new string.

=A1&REPT(" ",17-LEN(A1))&B1

you will also need a proportional font otherwise letter size throws it.

--
HTH

Bob Phillips

"philcud" wrote in message
oups.com...
using functions not vb
if names in column a
formula in b1
=LEFT(A1&" ",17)
this adds 17 spaces to the name, then returns the left 17 characters of
this new string.

Thanks, was about to write back that I needed a font that would print
spaces at same width as letters. Have looked through ones installed but
no luck. Any suggestions?

Bob Phillips wrote:
=A1&REPT(" ",17-LEN(A1))&B1

you will also need a proportional font otherwise letter size throws it.

Courier New

You want a monospaced font, not a proportional font.

--
Regards,
Tom Ogilvy


"noyb" wrote in message
...
Thanks, was about to write back that I needed a font that would print
spaces at same width as letters. Have looked through ones installed but
no luck. Any suggestions?

Bob Phillips wrote:
=A1&REPT(" ",17-LEN(A1))&B1

you will also need a proportional font otherwise letter size throws it.

Thanks again, missed it because of how it shows on screen. Prints fine
though.

Tom Ogilvy wrote:
Courier New

You want a monospaced font, not a proportional font.

--
HTH

Bob Phillips

"Tom Ogilvy" wrote in message
...
Courier New

You want a monospaced font, not a proportional font.

--
Regards,
Tom Ogilvy


"noyb" wrote in message
...
Thanks, was about to write back that I needed a font that would print
spaces at same width as letters. Have looked through ones installed but
no luck. Any suggestions?

Bob Phillips wrote:
=A1&REPT(" ",17-LEN(A1))&B1

you will also need a proportional font otherwise letter size throws
it.

Oops, meant the non-proportional.

--
HTH

Bob Phillips

"Tom Ogilvy" wrote in message
...
Courier New

You want a monospaced font, not a proportional font.

--
Regards,
Tom Ogilvy


"noyb" wrote in message
...
Thanks, was about to write back that I needed a font that would print
spaces at same width as letters. Have looked through ones installed but
no luck. Any suggestions?

Bob Phillips wrote:
=A1&REPT(" ",17-LEN(A1))&B1

you will also need a proportional font otherwise letter size throws
it.