Prev: Put excel data on a US map
Next: Formula Help
From: Gord Dibben on 8 Apr 2010 10:49
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

On Wed, 7 Apr 2010 22:01:01 -0700, John K <JohnK(a)discussions.microsoft.com>
wrote:

>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" wrote:
>
>> 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
>>
>>
>>
>> On Wed, 7 Apr 2010 15:50:01 -0700, John K <JohnK(a)discussions.microsoft.com>
>> wrote:
>>
>> >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?
>>
>> .
>>

First  |  Prev  | 
Pages: 1 2
Prev: Put excel data on a US map
Next: Formula Help