Using if statement to insert spaces

[kac1125]

I am trying to figure a formula to insert spaces depending on the number of digits in a given cell. For example A1 has a 6, I need B2 to be a cell that has 6 spaces in it so I can then do A1&B2 to show as 0000006 (0's indicate spaces) Also, fore example if A1 was 12 I would need B2 to be a cell that shows 5 spaces so I can then do A1&B2 to show 0000012 (again 0's indicate spaces). SO in summary Column be would be cells with only spaces. Please let me know if this makes sense and if you have any ideas for me.

Thanks!

 

[NeonRedSharpie]

Function addSpace(rng As Range) As String

    For x = 1 To 6 - Len(rng.value)
        addSpace = addSpace & " "
    Next x
    
    addSpace = addSpace & rng.value


End Function

Here's a custom function to do that if you don't want an ugly nested if statement. You could even modify it (to the below code) to be variable with the number of spaces.

Function addSpace(rng As Range, length As Long) As String

    For x = 1 To length - Len(rng.value)
        addSpace = addSpace & " "
    Next x
    
    addSpace = addSpace & rng.value


End Function

 

[VoG]

I don't think that you need column B to contain only spaces. How about

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

 

[Scott Huish]

Or this, put 7 spaces between the quotes:

=RIGHT("       "&A1,7)

 

[kac1125]

Thanks everyone for responding! VoG yours was exactly what I was looking for! Thanks again! I really appreciate your help, saves me loads of time!