CODEWITHSUNDEEP
excelvbaunicode
REPuzzle
REPuzzle

Reputation: 3

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

I use VBA to format my spreadsheets. The units have their own format.

How do I detect the ohm symbol? (According to character map = U+03A9)

Extract from a typical sheet

I can write it as R, or as "ohm", but is there was a method of the level

if left(string,1) = something here then

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 compare the cell with each member of the array.

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.

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

Correct formatting result for Unicode characters
enter image description here

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.
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

Upvotes: 0

Views: 745

Answers (1)

Ike
Ike

Reputation: 13044

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.

Upvotes: 1

Related Questions