asked on 12/12/2014

how to remove many commas from a text file...

hi Folks
I have a customer who imports a text file from a JD Edwards system and it comes in with many many commas e.g.
item,,,,,,,,,widget,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,thing,,,,,,,,,,,,,,,,,,,,,,,
(of course the number of commas is not consistent....:-)
Any suggestions on how she could clean up the file...thanks. :-)

Microsoft Excel

Last Comment

agwalsh

12/31/2014

SOLUTION

Rgonzo1971

12/12/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Qlemo

12/12/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Rgonzo1971

12/12/2014

with vba it would be

With Selection
    Set c = .Find(",,", LookIn:=xlValues)
    If Not c Is Nothing Then
        Do
            c.Replace What:=",,", Replacement:=",", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With

Open in new window

Regards

Rob Henson

12/12/2014

Use the Text to Columns functions with comma as a separator. Ensure the "Treat multiple separators as one" option is ticked.

Thanks
Rob H

agwalsh

12/12/2014

ASKER

@Rob, I actually tried that one and it didn't seem to work. But will suggest again...will pass on all suggestions.. thank you :-)

ASKER CERTIFIED SOLUTION

Rob Henson

12/12/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

agwalsh

12/31/2014

ASKER

This option worked for me - the Text to columns option when I tried it again and put the excess delimiter in the Other box. Great stuff.