character spacing in excel

[A Mackay]

how do I change the character spacing in a single column
in excel? I need to display the font as if there were a
spaace between each character. As each row within the
column contains different data format painter does not
seem to work as it copies the data from the original
cell. Has anyone any ideas?

 

[Dave Peterson]

I don't think I've seen a format for this kind of thing, but maybe you could
insert an extra space inbetween each character.

A macro like this might make it less painful.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim Str1 As String
Dim Str2 As String
Dim iCtr As Long

For Each myCell In Intersect(Selection, ActiveSheet.UsedRange).Cells
With myCell
If Trim(.Value) = "" Then
'do nothing
Else
Str1 = myCell.Value
Str2 = Space(Len(Str1) * 2 - 1)

For iCtr = 1 To Len(Str1)
Mid(Str2, iCtr * 2 - 1, 1) = Mid(Str1, iCtr, 1)
Next iCtr

myCell.Value = Str2
End If
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[A Mackay]

Thanks Dave
I'll try that and see how it goes.

-----Original Message-----
I don't think I've seen a format for this kind of thing, but maybe you could
insert an extra space inbetween each character.

A macro like this might make it less painful.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim Str1 As String
Dim Str2 As String
Dim iCtr As Long

For Each myCell In Intersect(Selection, ActiveSheet.UsedRange).Cells
With myCell
If Trim(.Value) = "" Then
'do nothing
Else
Str1 = myCell.Value
Str2 = Space(Len(Str1) * 2 - 1)

For iCtr = 1 To Len(Str1)
Mid(Str2, iCtr * 2 - 1, 1) = Mid (Str1, iCtr, 1)
Next iCtr

myCell.Value = Str2
End If
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



--

Dave Peterson
(e-mail address removed)
.

 

[A Mackay]

I have just tried the macro and it works perfectly -
thanks again.
I needed to format a spreadsheet containing 37,000+
records for a variable data printing programme which,
without your macro, would have been impossible. You've
saved my reputation.
Regards and thanks
Alasdair Mackay