如何在Excel中反向搜索? [英] How to reverse search in Excel?

问题描述

我在工作表中有一个文本，例如:

I have a text in a worksheet like:

这个女孩很漂亮

The girl is very beautiful

我希望公式从右到左搜索非常"一词，如果找到，则将其提取到工作表的其他区域.

I want a formula to perform a search from right to left for the word "very", and if found then extract it to some other region of the sheet.

注意:进行反向搜索的目的是因为我想在需要反向搜索的工作簿中实现它.

Note: Purpose of doing reverse search is because I want to implement it in my workbook which requires reverse search.

至少，请告诉我如何还原这样的文本:

At least, say me how to revert the text like this :

美丽是女孩

beautiful very is girl The

然后我可以进行常规搜索.我不知道VBA，所以请提供一些公式.

Then I can do a normal search. I don't know VBA so please give some formula.

推荐答案

VBA用于反转文本中单词的功能:

Public Function StrReverse(strIn As String, Optional Delimiter As String = " ") As String
    'Reverse the words in 'StrIn', split on a "Space" unless 'Delimiter' is specified
    Do While InStrRev(strIn, Delimiter) <> 0
        StrReverse = StrReverse & Delimiter & Right(strIn, Len(strIn) - InStrRev(strIn, Delimiter))
        strIn = Trim(Left(strIn, InStrRev(strIn, Delimiter) - 1))
    Loop
    StrReverse = Trim(StrReverse & Delimiter & strIn)
    If Left(StrReverse, 1) = Delimiter Then StrReverse = Right(StrReverse, Len(StrReverse) - 1)
End Function

例如，如果单元格A1包含:

For example, if cell A1 contains:

这个女孩很漂亮

The girl is very beautiful

...然后您可以在另一个单元格中输入:

...then you could enter in another cell:

=StrReverse(A1)

...将返回:

美丽是女孩

beautiful very is girl The

---

要将自定义VBA功能添加到工作簿中:

• 将要添加的功能的代码复制到Excel中(从上方).

  ---

  To add a custom VBA function to a workbook:

  • Copy the code for the function you want to add to Excel (from above).

    在Excel工作簿中，按 Alt + F11 打开VBA编辑器(VBE).

    In an Excel, workbook, press Alt + F11 to open the VBA Editor (VBE).

    按 Alt + I M 插入新模块.

    Press Alt + I M to insert a new module.

    按 Ctrl + V 粘贴代码.

    按 Alt + F C 返回Excel.

    Press Alt + F C to return to Excel.

    编辑#1 :

    为上述功能添加了可选的定界符(默认为"空格).

    Added optional delimiter to function above (defaults to a " " space).

    此外，FindReverse(如下)，它允许在工作表上使用VBA(鲜为人知的)InStrRev函数.

    Also, FindReverse (below), which allows VBA's (little-known) InStrRev function to be used on worksheets.

    Public Function FindReverse(StringCheck As String, StringMatch As String, _
        Optional Start As Long = -1) As Long
    
        'Returns the position number of the last occurrence of 'Stringmatch" 
        'within StringCheck',  Optionally specify the position number from the
        'end to begin the search.  (-1 = Begin at the end)
    
        FindReverse = InStrRev(StringCheck, StringMatch, Start)
    
    End Function
    

    ---

    编辑#2 :

    _{LOL @ Myself ...我总是告诉人们不要尝试重新创建MS Office中已经内置的功能，而且看来我无意间做了同样的事情-甚至提供与现有VBA功能相同的功能.}

    _{LOL @ Myself ... I'm always telling people not to try to recreate functionality that's already built into MS Office, and it seems that I unwittingly did the same thing -- even giving it the same as the existing VBA Function.}

    我意识到它的功能与我上面编写的StrReverse函数不同，但是我怀疑它也可以解决OP的原始查询...

    I realize that it's not identical functionality as the StrReverse function I wrote (above) but I suspect it also could have solved OP's original inquiry...

    尽管如此，我真的很惊讶，甚至VBA甚至允许 一个自定义函数与内置函数同名！

    Nonetheless, I am really surprised that VBA even allows a custom function to have the same name as a built-in function!

    _{如何混淆VBA:}

    这篇关于如何在Excel中反向搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！