英文:
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毫米)
在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)
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)
)
Algorithm
- Replace the line feed with
~!
- Enables splitting on
space
andtilde
, leaving the!
to replace the linefeeds when we rejoin the string
- Enables splitting on
- 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 theFILTER
function.
- We use the
- Note that when
- 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
orfrom 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"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论