Adding unknow text to numbers formatting

  • Thread starter Thread starter scottatrobins
  • Start date Start date
S

scottatrobins

I am trying to format a column with a combination of numbers and letter
(specifically 0000-00-000-0000XX) and I have everything down except ho
to add the XX to the format when I don't know what XX will be. Is ther
a "any alpha in this postion" command
 
scottatrobins said:
I am trying to format a column with a combination of numbers and
letters (specifically 0000-00-000-0000XX) and I have everything down
except how to add the XX to the format when I don't know what XX will
be. Is there a "any alpha in this postion" command?
Click to expand...

No.
Adding "any alpha" to a number makes it text. Text cannot be formatted,
only numbers.
You *could* format a number as 0000-00-000-0000"XX" but then the last
two are /always/ XX. And that is not what you want, I guess.

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.
 
I am trying to format a column with a combination of numbers and letters
(specifically 0000-00-000-0000XX) and I have everything down except how
to add the XX to the format when I don't know what XX will be. Is there
a "any alpha in this postion" command?
Click to expand...

You should try Worksheet_Change(ByVal Target As Range)
It may look like this :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Left(Target.Value, 4) & "-" & Mid(Target.Value, 5, 2)
& "-" & Mid(Target.Value, 7, 3) & "-" & Mid(Target.Value, 10, 4) &
Right(Target.Value, Len(Target.Value) - 13)
Application.EnableEvents = True
End Sub

Now all you have to do is to add some error handling and maybe write
your own formatting function
 
Back
Top