How To Delete All Commas Found After First Comma On A Line

[Spellbot5000]

Hey everyone. I'll a complete newb when it comes to Excel, but nevertheless I'm still the most skilled person with a computer in my department so I've been tasked with a project.

I have a long list of item names in a single column, and the names are filled with commas. I'm trying to figure out how to strip out all commas except the very first one found on each line. So I have a list like:

Retr, Cheek, Bishop
Frcp, Tissue, 1x2, 5.5IN
Frcp, Tissue, Adson, 1x2
Handle, Scalpel, #3
Suction, Rosen, 1.5, ENT
Suction, Adson, 5.5IN, 12fr
Obturator, Suction, Adson, 5.5IN, 12fr

And I would need the finished list to look like:

Retr, Cheek Bishop
Frcp, Tissue 1x2 5.5IN
Frcp, Tissue Adson 1x2
Handle, Scalpel #3
Suction, Rosen 1.5 ENT
Suction, Adson 5.5IN 12fr
Obturator, Suction Adson 5.5IN 12fr

Can anyone provide some tips or perhaps a formula I can use to perform this action. Any info would be greatly appreciated, thanks!

 

[Rick Rothstein]

Give this formula a try...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",","|",1),",",""),"|",",")

 

[Rick Rothstein]

Give this formula a try...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",","|",1),",",""),"|",",")

Or were you looking to physically change your actual data in place within their current cells? If so, you can do that with a macro...

Sub RemoveExtraCommas()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & _
                         Addr & ","","",""|"",1),"","",""""),""|"","",""),"""")")
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RemoveExtraCommas) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[Spellbot5000]

The macro worked perfectly! I altered it a little for the column I was working in, and it did the job flawlessly. Thanks again!