Link to home
Create AccountLog in
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
Avatar of ExcelGuide
ExcelGuide
Flag of Netherlands image

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

=LEFT(A1,FIND(",",A1,FIND(",",A1)+1)-1)
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

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

Ritesh
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
Avatar of arthurh88
arthurh88

ASKER

a work of beauty!