How do I add leading spaces to a value?

I have a list of values that vary in length, but I need to convert all of
them to 10 characters with spaces, not 0's as prefixes.

I have been able to use the text command to add 0's, but unable to add spaces.

Thanks in advance for any help!

-Chris

Hi Chris,

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

--
Kind regards,

Niek Otten

"Chris Brown" <[email protected]> wrote in message
news:[email protected]...
>I have a list of values that vary in length, but I need to convert all of
> them to 10 characters with spaces, not 0's as prefixes.
>
> I have been able to use the text command to add 0's, but unable to add
> spaces.
>
> Thanks in advance for any help!
>
> -Chris

Try something like this:

For a value in A1
B1: =RIGHT(TEXT(A1," 0"),10)

Note: That format includes 10 spaces before the 0.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Chris Brown" wrote:

> I have a list of values that vary in length, but I need to convert all of
> them to 10 characters with spaces, not 0's as prefixes.
>
> I have been able to use the text command to add 0's, but unable to add spaces.
>
> Thanks in advance for any help!
>
> -Chris

Hi

Try something like this:
=REPT(" ",10-LEN(A2))&A2

Once done, you'll have to Copy the range and then Edit/Paste Special/Values
to fix the values.

Hope this helps.
Andy.

"Chris Brown" <[email protected]> wrote in message
news:[email protected]...
>I have a list of values that vary in length, but I need to convert all of
> them to 10 characters with spaces, not 0's as prefixes.
>
> I have been able to use the text command to add 0's, but unable to add
> spaces.
>
> Thanks in advance for any help!
>
> -Chris

=TEXT(A1,"??????????")

Vaya con Dios,
Chuck, CABGx3



"Chris Brown" wrote:

> I have a list of values that vary in length, but I need to convert all of
> them to 10 characters with spaces, not 0's as prefixes.
>
> I have been able to use the text command to add 0's, but unable to add spaces.
>
> Thanks in advance for any help!
>
> -Chris

THAT was it!! Thanks for the help - have a great w/e!

"Ron Coderre" wrote:

> Try something like this:
>
> For a value in A1
> B1: =RIGHT(TEXT(A1," 0"),10)
>
> Note: That format includes 10 spaces before the 0.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Chris Brown" wrote:
>
> > I have a list of values that vary in length, but I need to convert all of
> > them to 10 characters with spaces, not 0's as prefixes.
> >
> > I have been able to use the text command to add 0's, but unable to add spaces.
> >
> > Thanks in advance for any help!
> >
> > -Chris