Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to format text and numbers as custom
How do you format a drivers license number? For example:
Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text? Do you have to use a macro? If so, does anyone have the code for this sequence? |
#2
|
|||
|
|||
If always H then custom format of "H"&000-etc should do it
or format as text and type in the - - - - or a worksheet change event to convert based on any entered letter and numbers entered witout - - - -- Don Guillett SalesAid Software "Julian Ganoudis" <Julian wrote in message ... How do you format a drivers license number? For example: Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text? Do you have to use a macro? If so, does anyone have the code for this sequence? |
#3
|
|||
|
|||
The "H" is just an example. In Florida, a drivers license # always starts
with the first letter of a persons last name, followed by twelve digits in a sequence of 3,3,2,3,1...for example: John Doe is: "D545-360-49-586-0". I want to be able to enter the letter and numbers in a single cell without having to input the dashes to speed up data entry. The problem is that if I use an @ symbol to allow text, then I can't use the # symbol (or so it seems). A dash could come after the letter if that would help (D-545). "Don Guillett" wrote: If always H then custom format of "H"&000-etc should do it or format as text and type in the - - - - or a worksheet change event to convert based on any entered letter and numbers entered witout - - - -- Don Guillett SalesAid Software "Julian Ganoudis" <Julian wrote in message ... How do you format a drivers license number? For example: Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text? Do you have to use a macro? If so, does anyone have the code for this sequence? |
#4
|
|||
|
|||
Hi Julian,
The following macro will format based on the text value of cell so it should work on cells that have text, numbers, formatted numbers with leading zeros. It will make no difference that the first character is a letter. If it say commas or decimal points they would just be characters. the only thing this does is insert hyphens based on your pattern. Sub Format_with_hyphens(Optional formatSTR As String) '-- The parameter here can only be used if invoked from another macro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim i As Long, j As Long, result As String Dim cell As Range, newStr As String, tstLen As Long On Error Resume Next 'In case no cells in selection If formatSTR = "" Then newStr = "Hxxx-xxx-xx-xxx-x" Else newStr = formatSTR End If tstLen = Len(Replace(newStr, "-", "")) For Each cell In Intersect(Selection, ActiveSheet.UsedRange) If Len(cell.Text) = tstLen Then j = 1 result = "" For i = 1 To Len(newStr) If Mid(newStr, i, 1) = "-" Then result = result & "-" Else result = result & Mid(cell.Text, j, 1) j = j + 1 End If Next i cell.Value = "'" & result End If Next cell Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Julian Ganoudis" <Julian wrote in message ... How do you format a drivers license number? For example: Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text? Do you have to use a macro? If so, does anyone have the code for this sequence? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a cell with numbers and user defined text | Excel Discussion (Misc queries) | |||
Custom Format text XX:XXXX:XX | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
How do i change numbers in text format to number format? | New Users to Excel |