Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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) "John K" 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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) -- Regards Dave Hawley www.ozgrid.com "John K" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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 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? . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding spaces
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 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 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another question regarding matching names and adding spaces | Excel Worksheet Functions | |||
Limiting Characters in a cell and adding spaces to fill the remain | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Adding Spaces to the end of a value | Excel Worksheet Functions | |||
Adding spaces to a cell | Excel Discussion (Misc queries) |