Here is what I'm trying to do:
这是我正在尝试做的事情:
Starting with a cell that has something like "receipt 28.19 walmart" or "receipt 117.58 amazon", I would like to be able to extract the dollar amount to a different cell. So a function that will put 28.19 and 117.58 in each cell, respectively.
从具有“收据28.19沃尔玛”或“收据117.58亚马逊”之类的单元格开始,我希望能够将美元金额提取到不同的单元格。因此,一个函数将分别在每个单元格中放置28.19和117.58。
I know you can use the MID function to extract part of a string of text if you know where it starts and how long it is, but the problem here is it could be different lengths. For example, the 28.19 is length 5, but the 117.58 is length 6, so I couldn't use MID to always get the total dollar amount because the length of the desired substring is not always the same.
我知道你可以使用MID函数来提取文本字符串的一部分,如果你知道它的起始位置和持续时间,但问题在于它可能是不同的长度。例如,28.19的长度为5,但117.58的长度为6,因此我无法使用MID总是获得总金额,因为所需子字符串的长度并不总是相同。
If anyone knows a solution to this, please respond. Also, if there is a solution for it that works in google spreadsheets, that would be acceptable as well. I'm assuming a lot of the functions are the same from excel to google sheets.
如果有人知道解决方案,请回复。此外,如果有一个适用于谷歌电子表格的解决方案,那也是可以接受的。我假设很多功能都是相同的从excel到google表。
4 个解决方案
#1
1
You want the number between the two blank space in the string given in the cell. So, for string like "receipt 2356.14789 amazon", we have to find pos of blanks.
您想要单元格中给出的字符串中两个空格之间的数字。因此,对于像“收据2356.14789亚马逊”这样的字符串,我们必须找到空白的位置。
Now, to find the position of nth space in a string we can use these formulas
现在,为了找到字符串中第n个空格的位置,我们可以使用这些公式
1st Position : =find(" ",a1)
2nd Position : =find(" ",a1,find(" ",a1)+1)
3rd Position : =find(" ",a1,find(" ",a1,find(" ",a1)+1)+1)
...........
Nth position : =find(" ",a1,find(" ",a1,find(" ",a1,find(" ",a1,......n-1 times find(" ",a1)+1)+1)+1.....n-1 times +1)
So, using above formula finding first and second blank in the string :
因此,使用上面的公式查找字符串中的第一个和第二个空白:
The values we have 8 and 19 in B1 and C1 are the positions of the blank spaces in the string. Now our desired result need to be, let string be s then
我们在B1和C1中有8和19的值是字符串中空格的位置。现在我们需要的结果是,让字符串成为s
s with removal of "receipt " & " amazon"
Now we can use the MID function to get the output. MID function : =MID(text,start_num,num_chars) returns the character from the middle of a text string, given a starting position and length.
现在我们可以使用MID函数来获取输出。 MID函数:= MID(text,start_num,num_chars)返回文本字符串中间的字符,给定起始位置和长度。
So, we have starting position i.e first blank position(8), we have to calculate the num_chars i.e the length.
因此,我们有起始位置,即第一个空白位置(8),我们必须计算num_chars,即长度。
To calculate length we have to do following calculation :
要计算长度,我们必须进行以下计算:
subtract the len of "amazon" from total len of string
length of substring s2 : =len(a1)- FIND(" ",A1,FIND(" ",A1)+1) which is second blank pos.
add len of sub string1 and sub string2
s2 + FIND(" ",A1)
Now, using mid function we will get,
现在,我们将使用mid函数,
=MID(A1,FIND(" ",A1),F1,(LEN S1+ LEN S2))
Now trim this value since it will be containing a blank space in start of "receipt ".
现在修剪此值,因为它将在“收据”的开头包含一个空格。
=trim(MID(A1,FIND(" ",A1),F1,(LEN S1+ LEN S2)))
So, final formula is below :
所以,最终公式如下:
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)+FIND(" ",A1)))
2nd Method :
第二种方法:
Use Delimit with select space. Shortcut ALT-> A->A->E , delimit select space , next then enter.
使用Delimit选择空格。快捷键ALT-> A-> A-> E,分隔选择空格,然后输入。
Output :
#2
2
If you can guarantee that the format is [word][space][number][space][word], then this should work:
如果你可以保证格式是[word] [space] [number] [space] [word],那么这应该有效:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (2-1)*LEN(A1)+1, LEN(A1)))
= TRIM(MID(SUTERTITUTE(A1,“”,REPT(“”,LEN(A1))),(2-1)* LEN(A1)+ 1,LEN(A1)))
Where A1 has "receipt 28.19 walmart"
A1有“收据28.19沃尔玛”的地方
#3
0
Consider the following. Take a look at how I went step by step to find the answer. =MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",MID(A3,SEARCH(" ",A3)+1,LEN(A3)))-1). If you want to learn more, checkout this free lesson Len(), Search(), Trim() (link expires in 10 days).
考虑以下。看看我是如何一步一步找到答案的。 = MID(A3,SEARCH(“”,A3)+ 1,SEARCH(“”,MID(A3,SEARCH(“”,A3)+ 1,LEN(A3))) - 1)。如果您想了解更多信息,请查看免费课程Len(),Search(),Trim()(链接在10天后到期)。
#4
0
The simplest way and my personal favorite method of doing this is using regex extract:
最简单的方法和我个人最喜欢的方法是使用正则表达式提取:
=REGEXEXTRACT ("YOUR TEXT", "\D+(\d+\.?\d+)\D+?")
This is basically saying your string starts with any number of non digit characters \D+ followed by some digits \d+ , which may or may not include a decimal with another digit , possibly followed by more non digit characters
这基本上是说你的字符串以任意数量的非数字字符开头\ D +后跟一些数字\ d +,其中可能包含或不包含带有另一个数字的小数,可能后跟更多的非数字字符
The parentheses around the digit patterns are what directs it to extract that value only
数字模式周围的括号指示它仅提取该值
Note this is for Google sheets specifically since you said you were also wanting one on there
请注意,这是专门用于Google表格的,因为您说您也想要一张
If you need to be able to sum your extracted text you can just wrap it in value:
如果您需要能够对提取的文本求和,您可以将其包装在值中:
=VALUE(REGEXEXTRACT (A1 , "\D+(\d+\.?\d+)\D+?"))
OR for even MORE convenience if you have a whole column of values you can enter a single formula that will run your regex all the way down:
或者为了更方便,如果你有一整列值,你可以输入一个公式来运行你的正则表达式:
=ARRAYFORMULA(IF(LEN(O1:O)>0,VALUE(REGEXEXTRACT (O1:O , "\D+(\d+\.?\d+)\D+?")),))
#1
1
You want the number between the two blank space in the string given in the cell. So, for string like "receipt 2356.14789 amazon", we have to find pos of blanks.
您想要单元格中给出的字符串中两个空格之间的数字。因此,对于像“收据2356.14789亚马逊”这样的字符串,我们必须找到空白的位置。
Now, to find the position of nth space in a string we can use these formulas
现在,为了找到字符串中第n个空格的位置,我们可以使用这些公式
1st Position : =find(" ",a1)
2nd Position : =find(" ",a1,find(" ",a1)+1)
3rd Position : =find(" ",a1,find(" ",a1,find(" ",a1)+1)+1)
...........
Nth position : =find(" ",a1,find(" ",a1,find(" ",a1,find(" ",a1,......n-1 times find(" ",a1)+1)+1)+1.....n-1 times +1)
So, using above formula finding first and second blank in the string :
因此,使用上面的公式查找字符串中的第一个和第二个空白:
The values we have 8 and 19 in B1 and C1 are the positions of the blank spaces in the string. Now our desired result need to be, let string be s then
我们在B1和C1中有8和19的值是字符串中空格的位置。现在我们需要的结果是,让字符串成为s
s with removal of "receipt " & " amazon"
Now we can use the MID function to get the output. MID function : =MID(text,start_num,num_chars) returns the character from the middle of a text string, given a starting position and length.
现在我们可以使用MID函数来获取输出。 MID函数:= MID(text,start_num,num_chars)返回文本字符串中间的字符,给定起始位置和长度。
So, we have starting position i.e first blank position(8), we have to calculate the num_chars i.e the length.
因此,我们有起始位置,即第一个空白位置(8),我们必须计算num_chars,即长度。
To calculate length we have to do following calculation :
要计算长度,我们必须进行以下计算:
subtract the len of "amazon" from total len of string
length of substring s2 : =len(a1)- FIND(" ",A1,FIND(" ",A1)+1) which is second blank pos.
add len of sub string1 and sub string2
s2 + FIND(" ",A1)
Now, using mid function we will get,
现在,我们将使用mid函数,
=MID(A1,FIND(" ",A1),F1,(LEN S1+ LEN S2))
Now trim this value since it will be containing a blank space in start of "receipt ".
现在修剪此值,因为它将在“收据”的开头包含一个空格。
=trim(MID(A1,FIND(" ",A1),F1,(LEN S1+ LEN S2)))
So, final formula is below :
所以,最终公式如下:
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)+FIND(" ",A1)))
2nd Method :
第二种方法:
Use Delimit with select space. Shortcut ALT-> A->A->E , delimit select space , next then enter.
使用Delimit选择空格。快捷键ALT-> A-> A-> E,分隔选择空格,然后输入。
Output :
#2
2
If you can guarantee that the format is [word][space][number][space][word], then this should work:
如果你可以保证格式是[word] [space] [number] [space] [word],那么这应该有效:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (2-1)*LEN(A1)+1, LEN(A1)))
= TRIM(MID(SUTERTITUTE(A1,“”,REPT(“”,LEN(A1))),(2-1)* LEN(A1)+ 1,LEN(A1)))
Where A1 has "receipt 28.19 walmart"
A1有“收据28.19沃尔玛”的地方
#3
0
Consider the following. Take a look at how I went step by step to find the answer. =MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",MID(A3,SEARCH(" ",A3)+1,LEN(A3)))-1). If you want to learn more, checkout this free lesson Len(), Search(), Trim() (link expires in 10 days).
考虑以下。看看我是如何一步一步找到答案的。 = MID(A3,SEARCH(“”,A3)+ 1,SEARCH(“”,MID(A3,SEARCH(“”,A3)+ 1,LEN(A3))) - 1)。如果您想了解更多信息,请查看免费课程Len(),Search(),Trim()(链接在10天后到期)。
#4
0
The simplest way and my personal favorite method of doing this is using regex extract:
最简单的方法和我个人最喜欢的方法是使用正则表达式提取:
=REGEXEXTRACT ("YOUR TEXT", "\D+(\d+\.?\d+)\D+?")
This is basically saying your string starts with any number of non digit characters \D+ followed by some digits \d+ , which may or may not include a decimal with another digit , possibly followed by more non digit characters
这基本上是说你的字符串以任意数量的非数字字符开头\ D +后跟一些数字\ d +,其中可能包含或不包含带有另一个数字的小数,可能后跟更多的非数字字符
The parentheses around the digit patterns are what directs it to extract that value only
数字模式周围的括号指示它仅提取该值
Note this is for Google sheets specifically since you said you were also wanting one on there
请注意,这是专门用于Google表格的,因为您说您也想要一张
If you need to be able to sum your extracted text you can just wrap it in value:
如果您需要能够对提取的文本求和,您可以将其包装在值中:
=VALUE(REGEXEXTRACT (A1 , "\D+(\d+\.?\d+)\D+?"))
OR for even MORE convenience if you have a whole column of values you can enter a single formula that will run your regex all the way down:
或者为了更方便,如果你有一整列值,你可以输入一个公式来运行你的正则表达式:
=ARRAYFORMULA(IF(LEN(O1:O)>0,VALUE(REGEXEXTRACT (O1:O , "\D+(\d+\.?\d+)\D+?")),))