How to remove selected text in column in excel? A lot of thanks in advance

Question

0.00/5 (No votes)

See more:

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

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.

Posted 17-Sep-16 18:08pm

Member 12734412

Updated 19-Sep-16 6:31am

Patrice T

v2

Add a Solution

Comments

Patrice T 18-Sep-16 0:35am

Show your code.
and show the result you want.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-09-17T21:03:00

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[^]

[EDIT]
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

Patrice T · Answer 2 · 2016-09-17T22:41:00

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.[^]

zamanipour · Answer 3 · 2016-09-19T06:31:00

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 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.

How to remove selected text in column in excel? A lot of thanks in advance

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0