开发者

Converting unicode fraction characters (vulgar fractions) to decimals in Excel 2003

开发者 https://www.devze.com 2023-03-28 20:25 出处:网络
I\'m trying to convert the following text to a decimal number in excel 2003: \"93⅛\" The output should be: 93.125

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消