adding spaces

[John K]

I have a cell that I want to say put in ab123456789us, but have it look like
ab 123 456 789 us. Is there away to do that?

 

[Mike]

Put this into cell A1 ab123456789us
Maybe this. Put into cell B1
=MID(A1,1,2)& " "&MID(A1,3,3)& " "&MID(A1,6,3)& " "&MID(A1,9,3)& "
"&MID(A1,12,2)

 

[Gord Dibben]

Not by formatting alone.............only true numbers will accept
formatting.

If only the one you could use custom format of "ab"000000000"us"

Then enter 123456789 in the cell.

But I'm sure ab123456789us is example only.

Can you post some more examples?

Might be able to work up some code for you.


Gord Dibben MS Excel MVP

 

[ozgrid.com]

The only way I can think of is via formulas;

=LEFT(H1,2) & " " & MID(H1,3,3) & " " &MID(H1,6,3) & " " &MID(H1,9,3) & " "
& RIGHT(H1,2)

 

[John K]

The thing is that the first 2 letters would change, but the us would always
be the same. I know in ms access you can do it and also have auto tabbing.
But in my office I'm the only one with ms access. And we're using office
2003.

John K

 

[Gord Dibben]

As long as string length is the same, this event code will work.

i.e. 13 characters

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
Dim oval As String
oval = Target.Value
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Value = Left(oval, 2) & Chr(32) & Mid(oval, 3, 3) _
& Chr(32) & Mid(oval, 6, 3) & Chr(32) & Mid(oval, 9, 3) _
& Chr(32) & Right(oval, 2)
End If
ws_exit:
Application.EnableEvents = True
End Sub

Place it in the sheet module.


Gord Dibben MS Excel MVP