Ignoring comma when creating validation lists

[white_flag]

Hello

it is possible to make a data validation list that will ignore commas?

(example) I have:
1,2|2,3|7,89| etc .....

To become
1,2
2,3
7,89
not
1
2
2
3
7
89


Thank you!

[snb]

Enter those values in A15:C14

Sub M_snb()
    Cells(1, 6).Validation.Add 3, , , "=A15:C15"
End Sub

[white_flag]

Hi snb,
thanks for your solution. But I am looking for a solution that will not gone involve a link to a range (without success)

I was trying to put my values in an variable array but the result is the same
1,2 become
1
2

[Zack Barresse]

Array, cell, what's the difference? Both house values in memory.

[Ringhal]

Is the comma a decimal point and the | symbol, a list separator?

[white_flag]

what ever list separator I choose (via regional settings) "| @ #" etc..
The result is the same instead of 0,3 on validation list I have 0 and 3. If I put "." instead of "," then the cell is not recognized as number (and I need to be a number).
anyway, I saw on internet this is not going.

[white_flag]

ok if I put decimal symbol "." insted of "," is gone go. But if I send a file to somebody else (with different settings) the file will fail.

[Ringhal]

Maybe try this:

1.2,2.3,7.89

[Aflatoon]

You can't have an actual comma ignored if you use a hardcoded list. You could use a symbol that looks like a comma - depending on how the list is to be used. (if it needs to match up with other cells, that's likely to be a problem)

[Zack Barresse]

The closest character I know of is 0130. To use this hold the ALT key and hit 0130 on your num pad. It's either that or use a range as described earlier. You can't use an actual comma. It won't be recognized as a number, you'll have to parse it out afterwards with a formula, like this...

=SUBSTITUTE(A1,CHAR(130),",")

HTH

[white_flag]

I try to put new regional settings wen the file is open and to restore the original settings when the file is closed (to make that trick . instead of ,)
It is changing the settings but not on current file.

ps. I try to upload the file (firefox and internet explorer) but I recieved errors.

anyway this is the code:

Option Explicit
Public blnRegionalSettingsChanged As Boolean
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetThreadLocale Lib "kernel32" () As Long

Private Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_SETTINGCHANGE As Long = &H1A
 
Const LOCALE_SDECIMAL As Long = &HE        'decimal separator
Const LOCALE_STHOUSAND = &HF  'thousand separator
Private old_LOCALE_SDECIMAL As String
Private old_LOCALE_STHOUSAND As String
Dim LCID As Long, iRet As Long, lpLCDataVar As String, Symbol As String
Dim iRet2 As Long, pos As Integer

Private Sub Workbook_Open()
    Call setRegionalSettings
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call restoreSettings
End Sub



Public Sub setRegionalSettings()
    
    blnRegionalSettingsChanged = False
   
' read decimal separator
    LCID = GetUserDefaultLCID()
    iRet = GetLocaleInfo(LCID, LOCALE_SDECIMAL, lpLCDataVar, 0)
    Symbol = String$(iRet, 0)
    iRet2 = GetLocaleInfo(LCID, LOCALE_SDECIMAL, Symbol, iRet)
    pos = InStr(Symbol, Chr$(0))
    If pos > 0 Then
        Symbol = Left$(Symbol, pos - 1)
    End If
    If Symbol <> "." Then
        'change decimal separator
        blnRegionalSettingsChanged = True
        old_LOCALE_SDECIMAL = Symbol
        LCID = GetUserDefaultLCID()
        Call SetLocaleInfo(LCID, LOCALE_SDECIMAL, ".")
        Call PostMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)
    End If
' read thousand separator
    LCID = GetUserDefaultLCID()
    iRet = GetLocaleInfo(LCID, LOCALE_STHOUSAND, lpLCDataVar, 0)
    Symbol = String$(iRet, 0)
    iRet2 = GetLocaleInfo(LCID, LOCALE_STHOUSAND, Symbol, iRet)
    pos = InStr(Symbol, Chr$(0))
    If pos > 0 Then
        Symbol = Left$(Symbol, pos - 1)
    End If
    
    If Symbol <> "," Then
        'change thousand separator
        blnRegionalSettingsChanged = True
        old_LOCALE_STHOUSAND = Symbol
        LCID = GetUserDefaultLCID()
        Call SetLocaleInfo(LCID, LOCALE_STHOUSAND, ",")
        Call PostMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)
    End If
End Sub
Public Sub restoreSettings()
    If old_LOCALE_SDECIMAL <> vbNullString Then
    ' restore decimal symbols
        LCID = GetUserDefaultLCID()
        Call SetLocaleInfo(LCID, LOCALE_SDECIMAL, old_LOCALE_SDECIMAL)
        Call PostMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)
    End If
    If old_LOCALE_STHOUSAND <> vbNullString Then
    ' restore thousand separator
        LCID = GetUserDefaultLCID()
        Call SetLocaleInfo(LCID, LOCALE_STHOUSAND, old_LOCALE_STHOUSAND)
        Call PostMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)
    End If
    
End Sub

[Zack Barresse]

It is my STRONG suggestion you abandon what you're trying to do and just use another cell with a formula (as shown) to parse out the special character.

[white_flag]

correct ...it is already a mess. I will do't via link to a range.
Thank you all!

[scotty562]

I realize this thread is from 2013, but I had the same question today. Here is how I solved it. I converted the comma that you would press on your keyboard to a unicode comma, Chr$(130). Then when I wanted to use the values in the value list again I simply converted it back. It looks identical, but Excel can tell them apart (SQL too I think).

Company = Company & Replace(rs!Company, ",", Chr$(130)) & "," 'Convert the comma to unicode
SearchCompany = Replace(Range("K5").Value, Chr$(130), ",") 'Convert it back