Detecting a specific Unicode text in an Excel cell from an array of possiblilities

I use VBA to do formatting of my spreadsheets. The units have their own format. I was wondering how to detect the ohm symbol. (According to character map = U 03A9)

I know I can write it as R, or as "ohm", but I have seen some brilliant tricks on this site and wondered if there was a really simple method of the level of- if left(string,1) = something here then ... that could do it.

An added complication is that I use arrays made up of strings from a single string and the SPLIT command, like this. LIST = " m mm mpm m/min s Pa kg N Nm mm/s Hz rpm GBP ul rad/s N/mm perc PC rev/s MPa psi N/mm2 mm2 " WrdArray() = Split(LIST, " ") Then I just compare the cell with each member of the array. Is there a method that works for that situation?

I thought of concatenating the unicode chars into this string, but then the whole thing would need to be a different string type, wouldn't it?

I am familiar with VBA but unicode chars, when the IDE does not seem to support them well, are a challenge.

Any help gratefully appreciated,, even just a quick pointer how to do it, and thanks for reading this.

I've just tried Ike's suggestion below and got this (in yellow), which is fantastic.

Correct formatting result for Unicode characters

The entering the symbols in the list was exactly as Ike said. Note here I am also adding a hybrid for the microFarad unit.

LIST_TAG2 = " " & ChrW(8486) & " " & ChrW(937) & " " & ChrW(&HB5) & "F "

Oddly though, the original routine to detect matches seems to still work. Here it is. ss is a string variable which is the cell text.

WrdArray4() = Split(UCase(LIST_TAG2), " ")

       For jj = LBound(WrdArray4) To UBound(WrdArray4)
        If WrdArray4(jj) = UCase(Trim(ss)) Then
            CELL_TYPE = IS_TAG2
            B = True
            End If
        Next jj

CodePudding user response：

If you want to check a certain cell value (e.g. selected cell) for the Ω-character use If AscW(selection) = 8486 ...

Regarding your list: You have to add the Ω-character as LIST = ChrW(8486) & " m mm " as you can't type it in the VBE editor.