在单元格中搜索两个数值并返回找到的第一个数值的位置。

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

Search two values in a single cell and return the position of the first one found

问题

=LEFT(C3180, IF(ISNUMBER(FIND(" ", C3180)), FIND(" ", C3180), FIND(CHAR(10), C3180))-1)

英文:

I have a column in Google Sheets where every cell in that column contains two words, i'm looking for some formula to extract only the first word, knowing that sometimes there is space between the two words and sometimes there is back to line instead of space.

I have tried this :
=LEFT(C3180, FIND(" ", C3180)-1)
And it worked in cells where there is a space between the two words.

I also tried this :
=LEFT(C3180, FIND(CHAR(10), C3180)-1)
And it worked in cells where there is back to line between the two words.

Finally i have tried this but it doesn't work :
=LEFT(C3180, FIND(OR(" ",CHAR(10)), C3180)-1)

答案1

得分: 0

这是一种方法:

=regexextract(A2,"(.*)(?: |\n)")

在单元格中搜索两个数值并返回找到的第一个数值的位置。

英文:

Here's one approach:

=regexextract(A2,"(.*)(?: |\n)")

在单元格中搜索两个数值并返回找到的第一个数值的位置。

答案2

得分: 0

Sure, here are the translated parts:

你可以在每个单元格中尝试以下任一公式:

=REGEXEXTRACT(B2:B31,"[a-zA-Z]+")

或者

=REGEXEXTRACT(B2:B31,"[[:alpha:]]+")

另一种方法是可以使用一个公式来处理整个范围

=INDEX(IFERROR(
REGEXEXTRACT(B2:B31,"[a-zA-Z]+")))

(请根据您的范围和区域设置调整每个公式)

英文:

You can try -in each cell- either of

=REGEXEXTRACT(B2:B31,"[a-zA-Z]+")

OR

=REGEXEXTRACT(B2:B31,"[[:alpha:]]+")

On the other hand, one can use a single formula for a whole range

=INDEX(IFERROR(
    REGEXEXTRACT(B2:B31,"[a-zA-Z]+")))

(Do adjust each formula according to your ranges and locale)

在单元格中搜索两个数值并返回找到的第一个数值的位置。

huangapple
  • 本文由 发表于 2023年5月14日 23:45:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/76248351.html
匿名

发表评论

匿名网友

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

确定