如何删除excel中列中的选定文本？非常感谢提前 [英] How to remove selected text in column in excel? A lot of thanks in advance

问题描述

c
-------------------------------------------------|
1  indraprasth hospital 153 Rates                |
-------------------------------------------------|
2  BLK Super Speciality Hospital 125 Rates       |
-------------------------------------------------|   
3  Saroj Super Speciality Hospital 86 Rates      | 
-------------------------------------------------|
.                                                | 
.                                                | 
.                                                |
.                                                | 

i想要删除153 Rates，125 Rates，86 Rates.. ..，从列的每个单元格c.how是否可以在excel 2010中？在此先感谢....



我尝试过：



我使用了trim函数。但它没有解决我的目的。

i want to remove "153 Rates","125 Rates","86 Rates "....,from each cell of the column c.how is that possible in excel 2010 ? thanks in advance....

What I have tried:

I have used trim function.but it is not solving my purpose.

推荐答案

你可以使用 Regex.Match 带模式的函数： \d {1，} Rate 检查单元格中的文本是否包含搜索文本。注意， d 是一个或多个数字实例。



请参阅： Microsoft Beefs Up VBScript with Regular表达式 [ ^ ]





VBA剧本：

You can use Regex.Match function with pattern: "\d{1,} Rates" to check if text in cell contains searched text. Note, d is one or more instance of digit.

See: Microsoft Beefs Up VBScript with Regular Expressions[^]


VBA script:

Option Explicit

Sub test()
    Dim i As Integer
    Dim wsh As Worksheet
    Dim sPat As String
    
    'pattern
    sPat = ".\d{1,} Rates"
    'working sheet
    Set wsh = ThisWorkbook.Worksheets(1)
    'starting row
    i = 2
    'loop through the rows
    Do While wsh.Range("C" & i) <> ""
        'insert replacement in col. D 
        wsh.Range("D" & i) = RegexReplace(wsh.Range("C" & i), sPat, "")
        i = i + 1
    Loop
    Set wsh = Nothing

End Sub


'needs reference MS VBSrcipt Regular Expressions 5.5
Function RegexReplace(ByVal sInput As String, ByVal sPattern As String, ByVal sReplacement As String)
    Dim sRetVal As String
    Dim oRe As RegExp
    
    Set oRe = New RegExp
    With oRe
        .Global = True
        .Pattern = sPattern
        sRetVal = .Replace(sInput, sReplacement)
    End With
    Set oRe = Nothing

    RegexReplace = sRetVal
End Function

作为解决方案1的补充：​​

Regex.Match 会告诉你表达式是否在字符串中。

Regex.replace 将进行替换。



以下是RegEx文档的链接：

perlre - perldoc.perl.org [ ^ ]

以下是帮助构建RegEx并调试它们的工具的链接：

.NET Regex Tester - Regex Storm [ ^ ]

Expresso正则表达式工具 [ ^ ]

这个显示RegEx是一个很好的图表，它非常有助于理解RegEx的作用：

Debuggex：在线可视正则表达式测试器。 JavaScript，Python和PCRE。 [ ^ ]

In complement to Solution 1:
Regex.Match will tell you if the expression is in the string.
Regex.replace will do the replacement.

Here is a link to RegEx documentation:
perlre - perldoc.perl.org[^]
Here is links to tools to help build RegEx and debug them:
.NET Regex Tester - Regex Storm[^]
Expresso Regular Expression Tool[^]
This one show you the RegEx as a nice graph which is really helpful to understand what is doing a RegEx:
Debuggex: Online visual regex tester. JavaScript, Python, and PCRE.[^]

您好，您可以在文本中找到一个模式并选择您想要的部分。

例如，如果您的模式是：选择文本直到医院结束然后执行此操作：

Hi, You can find a pattern in your text and select the part you want.
for example if your pattern is: select text until end of 'hospital' then do this:

=LEFT(A1;FIND("hospital";LOWER(A1))+8)

或者你的模式是：在第一个数字之前选择文字然后这样做：

or maybe your pattern is: select text before first number then do this:

=TRIM(LEFT(A1;MIN(IFERROR(FIND("1";A1);1000);IFERROR(FIND("2";A1);1000);IFERROR(FIND("3";A1);1000);IFERROR(FIND("4";A1);1000);IFERROR(FIND("5";A1);1000);IFERROR(FIND("6";A1);1000);IFERROR(FIND("7";A1);1000);IFERROR(FIND("8";A1);1000);IFERROR(FIND("9";A1);1000);IFERROR(FIND("0";A1);1000))-1))

（1000是一个很大的数字，可以帮助MIN函数找到数字字符的首次发生，如果没有数字也可以选择所有文本）



如果你想使用VB相反，如果Excel功能，那么其​​他答案是完整和伟大的。

(1000 is a big number to help MIN function to find first happening of number characters and also select all text if there were no digits)

if you like to use VBA instead if Excel functions then other answers are complete and great.

这篇关于如何删除excel中列中的选定文本？非常感谢提前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！