How to distribute characters in a cell?

G

Gary''s Student

Select the cells and run this macro:

Sub SpaceUm()
b = " "
For Each r In Selection
v = r.Value
l = Len(v)
t = ""
For i = 1 To l
t = t & Mid(v, i, 1) & b
Next
r.Value = t
Next
End Sub

Data like:

abc12333

will become:

a b c 1 2 3
 
C

churin

Thank you for your response. I have never used macro so that this is a
good opportunity for me to learn how to use macro.

Having said the above, I would like to ask the following:
There is an option of "Distributed(Indent)" for Text Alignmnet in Format
Cells. What does this option suppose to do?
 
B

Billy Liddel

Your best idea is to try it. If you have seen old Letters or old books for
that matter, the first line of a new paragraph was indented by 1/2 inch, 2.25
cm to make it stand out. Excel indents the text a few spaces from the left of
the cell. Use it at the beginning of a new Section in say a Profit & Loss
Account.

Gary's neat macro will pad out the spaces between each character but will
not neccessarily produce even character spacing. This depends on the font.
Yiou need a non proportional font such as Courier or FixdSys

Regards
Peter
 
C

churin

I did try it. All it did was just centering the text string and not
distributing anything. Another word "Distributed(Indent)" did exactly
the same thing as "Center" did. I wondered if there was something wrong
with my Excel 2007. I would think there must be a difference(s) in what
these format options do.
 
Top