Avatar of arthurh88
arthurh88 asked on

Delete text from an Excel column

I need to search each cell in a column, and if the text has 2 commas, I need to delete the second comma, and all text following the second comma

Smith, John, IT Technician

returns:   Smith, John


Doe, Jane

Returns:  Doe, Jane
.NET ProgrammingMicrosoft Excel

Avatar of undefined
Last Comment
arthurh88

8/22/2022 - Mon
ExcelGuide

Use the following code (tested and worked on your example)
Sub Button1_Click()
 
On Error GoTo errorHandler
 
Dim myDoc As Worksheet
Dim cel As Range
Set myDoc = ActiveSheet
 
    For Each cel In myDoc.UsedRange
        
    test = InStr(cel.Value, ",") + 1
    test1 = InStr(test, cel.Value, ",")
    test2 = Left(cel.Value, test1 - 1)
    cel.Value = test2
 
    Next
 
Exit Sub
errorHandler:
MsgBox Error
 
 
End Sub

Open in new window

riteshparakh

=LEFT(A1,FIND(",",A1,FIND(",",A1)+1)-1)
ASKER CERTIFIED SOLUTION
Saurabh Singh Teotia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ExcelGuide

IMHO using this code is much much faster, esspecially if you dont want to have the old data.
Use the code below to specify the column (my first code didnt have that)

anyway you can use formulas as well if you like.
Sub Button1_Click()
 
On Error GoTo errorHandler
 
Dim myDoc As Worksheet
Dim cel As Range
Set myDoc = ActiveSheet
 
    For Each cel In Columns("A:A").Cells
    test = InStr(cel.Value, ",") + 1
    test1 = InStr(test, cel.Value, ",")
    test2 = Left(cel.Value, test1 - 1)
    cel.Value = test2
 
    Next
 
 
errorHandler:
Exit Sub
 
End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
riteshparakh

Thanks saurabh :-) for pointing it out, i was about to post that.

Ritesh
mz3prs

Here is a file that has the formulas inside, just in case any of the above solutions do work.

You can copy the last cell and paste the values in the 1st column and then delete the other columns.
Names.xls
ASKER
arthurh88

a work of beauty!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.