cell format problem

N

necip

Hi there,
Is it possible to format cell for to make one character empty spac
between each character.
for example: if I write in to any cell H5F2 then must shown like
H 5 F 2

thanks in advance
neci
 
J

Jim May

If your data is only 4 digits you can use formula:
=LEFT(A1,1) & " " &MID(A1,2,1) & " " &MID(A1,3,1) & " " & MID(A1,4,1)
just a thought,,
 
R

Ron Rosenfeld

Hi there,
Is it possible to format cell for to make one character empty space
between each character.
for example: if I write in to any cell H5F2 then must shown like
H 5 F 2

thanks in advance
necip
Click to expand...

I don't know of any way to do this with formatting.

One approach would be to write an event-triggered VBA routine that adds the
spaces. This would, of course, turn the result into something different than
what you entered. In other words, H5F2 is not the same as H 5 F 2.

If you want to use that approach, the below code can do it. Set aoi to
whatever range(s) you want displayed this way.

To enter the code, right-click on the worksheet tab and select View Code.
Paste the code below into the window that opens.

==================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Dim i As Integer
Dim temp As String
Dim temp2()

Set aoi = [A:A] 'change this to reflect area to be formatted this way

Application.EnableEvents = False

If Not Intersect(Target, aoi) Is Nothing Then
For Each c In Target
If Not Intersect(c, aoi) Is Nothing Then
temp = Replace(c.Text, " ", "")
ReDim temp2(Len(temp))
For i = 0 To Len(temp) - 1
temp2(i) = Mid(temp, i + 1, 1)
Next i

c.Value = Join(temp2)
End If
Next c
End If

Application.EnableEvents = True
End Sub

========================================

--ron
 
Top