Format Text

  • Thread starter Thread starter Hughes
  • Start date Start date
H

Hughes

I'm trying to format order numbers in excel and I want dashes to fill in
automatically. The problem is I have numbers and text for each order. For
example: SRT46510D1234 is what I want to type in, but I want it to appear as
SRT456-10-D-1234. Is there a way to make that happen?
 
You can not have th eformat directly in the cell you are entering in. XL does
not have a facility for input masks or formats on text. You can output the
formatted text in a seperate cell with a formulas such as this

=LEFT(A1, 6) & "-" & MID(A1, 7, 2) & "-" & MID(A1, 9, 1) & "-" & MID(A1, 10,
256)
where your input was in cell A1
 
You can either use a formula in another cell, or translate them in place
automatically with an Event Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, t As Range
Dim v As String, s As String
Set A = Range("A:A")
Set t = Target
s = "-"
If Intersect(t, A) Is Nothing Then Exit Sub
v = t.Value
v = Left(v, 6) & s & Mid(v, 7, 2) & s & Mid(v, 9, 1) & s & Right(v, 4)
Application.EnableEvents = False
t.Value = v
Application.EnableEvents = True
End Sub

This will auto-translate entries made in column A.

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
A variation of TM's suggestion:

=REPLACE(REPLACE(REPLACE(A1,10,,"-"),9,,"-"),7,,"-")

The numbers are in largest to smallest, and refer to the position in the
original string.

(It made the counting easier for me.)
 
But an input mask is something that you can use in Access, right? Is there a
way to format it in Access even if the numbers and letters don't always
appear in the same place?
 
Yes you can set up an input mask in Access but they are static. Check on the
Access news group for all of the options in that part of the world...
 
Back
Top