Excel宏:如何从文本中提取货币和其他数字?

huangapple go评论140阅读模式
英文:

Excel Macro: How to strip currency from text with other numbrs?

问题

I will only provide translations for the non-code portions of your text:

帮帮忙!试图从Excel单元格中删除价格。金额中将始终包含'$'和/或'.'。问题出在1)可能有多种货币格式,2)单元格中还有其他数字,3)我不想删除回车符格式。

示例单元格内容:这应该删除$159.95和69.95

90,000英里
进气服务$159.95
空气滤清器69.95
后制动片(4毫米)

Excel宏:如何从文本中提取货币和其他数字?

在Notepad++中,这是一个易于查找/替换的正则表达式,但是我在工作中无法访问它。Excel是否有一种方法可以查找'$'并删除它,以及它后面的数字,直到下一个空格或回车符?

我找到的最接近的方法是使用MID或TEXTAFTER函数,但它还会删除'$'之后的所有数据,或者删除所有回车符。

英文:

Help! Trying to strip prices from a cell in Excel. Will always have either a '$' and/or a '.' in the amount. Problems arise since 1) there can be multiple currency formats, 2) there are other numbers in cell, 3) I don't want to strip out carriage return formatting

Example Cell Content: This should strip out $159.95 and 69.95

90,000 Mile
Intake Service $159.95
Air Filter 69.95
Rear Brake Pads (4MM)

Excel宏:如何从文本中提取货币和其他数字?

This is an easy find/replace regular expression in Notepad++, but I'm not allowed to access this at work. Does Excel have something to find '$' and remove it plus numbers after it up until the next space or carriage return?

The closest I've found is a MID or TEXTAFTER function but it also takes out all of the data after the '$' or it removes all the carriage returns.

....................

答案1

得分: 2

如果您拥有Microsoft 365:

=SUBSTITUTE(
    TEXTJOIN(
        " ",
        ,
        LET(
            a, SUBSTITUTE(A1, CHAR(10), "~!"),
            b, TEXTSPLIT(a, , {" ", "~"}),
            c, ISNUMBER(FIND({"$", "."}, b)),
            d, BYROW(c, LAMBDA(arr, OR(arr))),
            e, FILTER(b, NOT(d)),
            e
        )
    ),
    "~!",
    CHAR(10)
)

算法

  • 将换行符替换为 ~!
    • 可以在 空格波浪符 上进行分割,将 ! 用于重新连接字符串时替换换行符。
  • 过滤结果数组,删除包含 $. 的子字符串
    • 注意,当 find_text 是一个数组时,结果将是一个多列数组,每个元素对应一个列
      • 我们使用 BYROW 函数将其减少为单列数组,以在 FILTER 函数中使用。
  • 使用空格重新连接
  • 然后将 ~! 替换为换行符

如果您没有Microsoft 365,也可以使用Power Query来完成,Power Query在Windows Excel 2010+和Excel 365(Windows或Mac)中可用。

要使用Power Query:

  • 选择数据表中的某个单元格
  • 数据 => 获取和转换 => 从表/范围获取从工作表内获取
  • 当PQ编辑器打开时:主页 => 高级编辑器
  • 注意第2行中的表 名称
  • 将下面的M代码粘贴到原有代码的位置
  • 将第2行中的表名称更改回最初生成的名称。
  • 阅读代码和注释。算法与上面的公式非常相似
let

//将下一行更改为反映您的实际数据源
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"更改类型" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    #"删除货币" = Table.TransformColumns(#"更改类型",

//将每个单元格拆分为换行符
//然后将每个拆分再分割为空格
//删除包含$或.的项目
//重新组合
    {"Column1", (c)=>
       try
        let 
            lines = Text.Split(c, "#(lf)"),
            removeCurr = List.Transform(lines, (d)=>
                let 
                    words = Text.Split(d, " "),
                    notCurr = List.Select(words, each not Text.Contains(_, "$") and not Text.Contains(_, "."))
                in 
                    notCurr)
        in 
            Text.Combine(List.Transform(removeCurr, each Text.Combine(_, " ")), "#(lf)")

        otherwise null}
    )
in
    #"删除货币"
英文:

If you have Microsoft 365:

=SUBSTITUTE(
    TEXTJOIN(
        " ",
        ,
        LET(
            a, SUBSTITUTE(A1, CHAR(10), "~!"),
            b, TEXTSPLIT(a, , {" ", "~"}),
            c, ISNUMBER(FIND({"$", "."}, b)),
            d, BYROW(c, LAMBDA(arr, OR(arr))),
            e, FILTER(b, NOT(d)),
            e
        )
    ),
    "!",
    CHAR(10)
)

Excel宏:如何从文本中提取货币和其他数字?

Algorithm

  • Replace the line feed with ~!
    • Enables splitting on space and tilde, leaving the ! to replace the linefeeds when we rejoin the string
  • Filter the resultant array removing substrings that contain $ or .
    • Note that when find_text is an array, the result will be a multi-column array with one column for each element
      • We use the BYROW function to reduce this to a single column array for use in the FILTER function.
  • Rejoin with spaces
  • Then replace the ! with linefeeds

If you do not have Microsoft 365, this can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range or from within sheet
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the code and comments. The algorithm is very similar to the formula above
let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    #"Remove Currency" = Table.TransformColumns(#"Changed Type",

//split each cell on the line feed
// then split each split on space
// remove items that contain $ or .
// reassemble
    {"Column1", (c)=>
       try
        let 
            lines = Text.Split(c,"#(lf)"),
            removeCurr = List.Transform(lines, (d)=>
                let 
                    words = Text.Split(d," "),
                    notCurr = List.Select(words, each not Text.Contains(_,"$") and not Text.Contains(_,"."))
                in 
                    notCurr)
        in 
            Text.Combine(List.Transform(removeCurr, each Text.Combine(_," ")), "#(lf)")

        otherwise null}
    )
in
    #"Remove Currency"

huangapple
  • 本文由 发表于 2023年8月11日 03:26:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76878782.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定