Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    wildcards? (excel)

    I have a macro which makes all the contents in a cell a consistent format. i was wondering if anyone can recall the wildcard value in excel for unknown values.

    ex. my format is ###-##-##-###-##A#
    so if select 100-4-23-24-2a4M
    it becomes 100-04-23-024-02A4



    the # value is a wildcard here which is replaced by a number when i select a cell, if there is no number or value to fill in for the # then a O is printed.
    Unfortunetly # is not a wildcard so this doesnt work.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: wildcards? (excel)

    I don't think you can do that with a custom format. Could you post the code you already have?

    PS Your example seems inconsistent. Why should 2a4M turn into 02A4?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: wildcards? (excel)

    It might be related to Re: CONSISTENT COVERSION OF A CELL (EXCEL) which asked to convert to the type: "000-00-00-000-00W0" if all the numbers were not listed that was created. In this post a function was created.

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: wildcards? (excel)

    You can modify the function given in Re: CONSISTENT COVERSION OF A CELL (EXCEL) so it uses a "W" instead of an "A".

    You could make it more general by adding a 2nd parameter to be the letter at the end. instead of hard-coding the letter.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: wildcards? (excel)

    One way to use the custom format is to set the custom format as:
    000-00-00-000-00"A"0

    Then the user has to only enter a 13 digit number to have it display correctly (no dashes, spaces, or letters only 13 whole numbers...)

    You could create a validation (data - validation) to ensure that the number is a
    Whole Number
    Between
    1
    9999999999999

    This will insure leading zeroes, though it will not enter "intermediate zeroes" as was asked in the <post#=482832>post 482832</post#>.

    The other option is to create separate cells for each "sub entry" and validate each of them and then combine them into one cell concatenation. this would allow for adding intermediate zeroes since they would be "leading" zeroes in the sub-number...

    Steve

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: wildcards? (excel)

    I have taken a look at the code in the post you mentioned Steve, would you be able to explain this code you written im not sure what some of he functions are

    Option Explicit
    Function ConvertString(vValue)
    Dim sTemp As String
    Dim iDash As Integer
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    With AWF
    sTemp = vValue
    sTemp = .Substitute(sTemp, "/", "-")
    sTemp = .Substitute(sTemp, " ", "")
    sTemp = .Substitute(sTemp, "-W", "-00W")
    iDash = Len(sTemp) - Len(.Substitute(sTemp, "-", ""))
    Select Case iDash
    Case 3
    sTemp = .Substitute(sTemp, "W", "-00W")
    Case 4
    Case Else
    ConvertString = "Not enough dashes"
    Exit Function
    End Select
    sTemp = .Rept("0", 4 - InStr(sTemp, "-")) & sTemp
    sTemp = .Replace(sTemp, 5, 0, .Rept("0", 7 - InStr(5, sTemp, "-")))
    sTemp = .Replace(sTemp, 8, 0, .Rept("0", 10 - InStr(8, sTemp, "-")))
    sTemp = .Replace(sTemp, 11, 0, .Rept("0", 14 - InStr(11, sTemp, "-")))
    End With
    ConvertString = Left(sTemp, 18)
    Set AWF = Nothing
    End Function

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: wildcards? (excel)

    Does this help? Or do you have specific questions?

    Steve

    Function ConvertString(vValue)
    'Dims variables
    Dim sTemp As String
    Dim iDash As Integer
    Dim AWF As WorksheetFunction
    'AWF used to allow VB to use excel functions
    Set AWF = Application.WorksheetFunction
    With AWF
    'store the current text string
    sTemp = vValue
    'convert any "/" to "-"
    sTemp = .Substitute(sTemp, "/", "-")
    'Get rid of spaces
    sTemp = .Substitute(sTemp, " ", "")
    'Any "-W" should be "-00W" since it is a zero
    sTemp = .Substitute(sTemp, "-W", "-00W")
    'determines the number of dashes in the string
    iDash = Len(sTemp) - Len(.Substitute(sTemp, "-", ""))
    Select Case iDash
    Case 3
    'if there are 3 dashes convert the "W" to "-00W"
    sTemp = .Substitute(sTemp, "W", "-00W")
    Case 4
    'if 4 dashes do nothing
    Case Else
    'if any other number give a message and leave function
    ConvertString = "Not enough dashes"
    Exit Function
    End Select
    'Find each section and Pad with zeroes
    'InStr finds the first occurence of the "-" after the initial value
    'Rept repeats the "0" the appropriate number of times
    'Replace acts like an Insert when third parameter is 0
    'Instr is VB function see VB help for more
    'Replace and Rept are excel functions see XL help for more
    sTemp = .Rept("0", 4 - InStr(sTemp, "-")) & sTemp
    sTemp = .Replace(sTemp, 5, 0, .Rept("0", 7 - InStr(5, sTemp, "-")))
    sTemp = .Replace(sTemp, 8, 0, .Rept("0", 10 - InStr(8, sTemp, "-")))
    sTemp = .Replace(sTemp, 11, 0, .Rept("0", 14 - InStr(11, sTemp, "-")))
    End With
    'take the left 18 chars
    ConvertString = Left(sTemp, 18)
    'clear the variable
    Set AWF = Nothing
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •