Converting unicode fraction characters (vulgar fractions) to decimals in Excel 2003
I'm trying to convert the following text to a decimal number in excel 2003:
"93⅛"
The output should be: 93.125
I've gotten this to work with ¼, ½, ¾ by using the Replace function in VBA: For example, this works:
cur开发者_StackOverflow社区_cell = Replace(cur_cell, "½", " 1/2")
However, the ⅛ and family characters are not supported in the VBA editor. They display as ??. Instead, I tried to replace the unicode value directly:
cur_cell = Replace(cur_cell, " & ChrW$(&H215B) & ", " 1/8")
But this doesn't work.
Is there a good way to convert these strings to numbers that I can use?
The correct syntax is:
cur_cell = Replace(cur_cell, ChrW$(&H215B), " 1/8")
Your example was saying: replace the string consisting of a space, an ampersand, a space [etc.] with 1/8
. Clearly that's not what you want to do!
I'd actually recommend:
cur_cell.Value = Replace(Replace(cur_cell.Value, ChrW$(&H215B), ".125")," ","")
to circumvent Excel's automatic replacement of fractions. I just don't like to rely on that kind of automatic stuff. Why not write it as a decimal number straight off? Also, I like explicitly refering to the cell's .Value
property as opposed to relying on it being the default property.
精彩评论