如何使用“下标”转换数字？分数到Excel中的小数？ [英] How can I convert numbers with "subscript" fractions to decimal in Excel?

问题描述

我有成千上万的数字以无法使用的格式提供给我进行计算：

 9⅝，9¼，9¾， 10¼
  

这是它们在Excel中每一行中的显示方式。我想将它们转换为小数。我已经尝试过 = FIXED（A2，3），但这并不能解决问题。出现问题是因为分数是这种奇怪的格式。

是否可以使用公式或宏来获取所需的十进制结果？

非常感谢复活节快乐！

解决方案

问题是您的每个分数都是一个unicode字符。如果我们在单元格中隔离单个字符并运行：

  Sub WhatsIt（）
 Dim v As Variant 
v = ActiveCell.Value 
 MsgBox Asc（v）& vbCrLf& AscW（v）& vbCrLf& Application.WorksheetFunction.Dec2Hex（AscW（v））
 End Sub 
  

我们看到：

现在可以轻松地将这些项目转换为真实价值。这是一个开始： b
d = CDbl（中间（s，1，透镜-1））
如果AscW（右边（s，1））= 8541然后d = d + 0.625
Konvert = d
结束函数

您必须扩展此代码以包含其他分数。以下是一些常见的示例：

< img src = https://i.stack.imgur.com/46YiB.png alt =在此处输入图片描述>

I have thousands of numbers that were given to me in an unusable format for calculations:

9⅝, 9¼, 9¾, 10¼

This is how they appear in each row in Excel. I would like to convert these to a decimal. I've tried =FIXED(A2, 3) but that did not do the trick. The problem arises because the fractions are in this weird format.

Is there a formula or macro that I can implement to get the desired decimal results?

Thank you very much and happy Easter!

解决方案

The problem is that each of your fractions is a single unicode character. If we isolate a single character in a cell and run:

Sub WhatsIt()
    Dim v As Variant
    v = ActiveCell.Value
    MsgBox Asc(v) & vbCrLf & AscW(v) & vbCrLf & Application.WorksheetFunction.Dec2Hex(AscW(v))
End Sub

we see:

It now becomes easy to convert these items into real values. Here is a start:

Public Function Konvert(s As Variant) As Double
    Dim d As Double

    d = CDbl(Mid(s, 1, Len(s) - 1))
    If AscW(Right(s, 1)) = 8541 Then d = d + 0.625
    Konvert = d
End Function

You must expand this code to include other fractions. Here are some common ones:

这篇关于如何使用“下标”转换数字？分数到Excel中的小数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！