Solved: Blank Null Space

[vzachin]

hi,

i have a very odd question.
i need to have a cell populated with only blank or null spaces based on a number in a different cell or based on the length of strings in another cell.

for example, if cell H9 = "XXXX", then i need cell H12 = "4blankspaces";
if cell I7 = 6, then i need cell I13 = "6blankspaces"
(i don't want the literal 4blankspaces or 6blankspaces, just the blanks...
i couldn't input H12 = " " or I13= " " with the correct spaces)

is this possible?


thanks
zach

[Bob Phillips]

Is this what you want#

=REPT(" ",LEN(H9)-LEN(SUBSTITUTE(H9,"X","")))

and

=REPT(" ",I7)

[vzachin]

hi bob,

thanks for the very interesting formula. didn't realize that existed.

i just came up with this
[vba]Sub testspace()
nbrspace = Range("h7")
Range("h12").ClearContents
Do
mycell = Len(Range("h12"))
If mycell <> nbrspace Then
Range("h12") = Range("h12") + Chr(160)
Else
Exit Sub
End If
Loop Until mycell = nbrspace
End Sub[/vba]