Double comma - multiple times in Cell Text. Unable to remove

[dubmartian]

Hi all,
Ive searched a few posts related to removing commas and Ive tried to adapt the answers to my situation with no luck so I am biting the bullet and adding to the forum fodder. Here is my situation.

I have about 6k rows of text where there are single comas separating values and at some point double commas. ( this happened when I replaced commas for carriage returns in the original data).
The double commas are what I would like to remove. They do not occur at the same positions through all 6k records.
If I do a ctrl F to find a comma it doesnt return any results although there are at least 10 commas per line. This also does not work for the double commas.
What I was able to do was remove the trailing comma left at the end of each record so I know they are real

So in general I would like to search for every instance of ",," and replace with just ","

Things that did not work for me so far:
=SUBSTITUTE(a1,",,",",")
CTRL H Find ,, and replace ,

Thanks in advance.

[TABLE="width: 1600"]
<tbody>[TR]
[TD="width: 1600"]10250T - Accessories
, 10250ED1065-2
, UPC:782113718811
,
, Height: X
, Length: X
, Width: X
, Manufacturer Warranties:
, 1 year
,
, Type: 10250T
, Size: 30 mm
, Product Category: Adapter for standard and master indicating light
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Try your Substitute looking for Char(130). It's a comma look alike. Commas typed from the keyboard are Char(44).

Perhaps like this:

=SUBSTITUTE(A1,CHAR(130)&CHAR(130),CHAR(44))

or

=SUBSTITUTE(A1,CHAR(130)&CHAR(130),",")

 

[alansidman]

Here is some VBA code that may help

Option Explicit


Sub FindCommas()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("A1:F4")    'change this range to what your range is
    For Each c In rng
        If InStr(1, c, ",,") > 0 Then
            c.Value = Replace(c, ",,", ",")
        End If
    Next c
End Sub

 

[dubmartian]

Interestingly I've tried both solutions which are very logical and neither remove the double comma.
In the example text above, the double occurs both at the end of the "UPC:782113718811 ,, Height"
and "1 year ,, Type".
Is it possible since the formula and the vb script did not remove anything, that there are other characters or invisible characters that are causing the comma to be invisible or appear to not be a comma?

 

[dreid1011]

Possibly Char(132)... it is a double comma char itself.

Otherwise, here is a list of invisible characters:

Char( 1, 9, 10, 13, 28, 29, 30, 31, 32, 129, 141, 143, 144, 157, 160 )

 

[dubmartian]

https://drive.google.com/file/d/0B0owtOOFvq0xVHVDdnpCR2hRVUE/view?usp=sharing

Would you be able to take a look and see if you all see anything that stands out and a possible fix.
Too many gray hairs right now. lol

Thanks in advance...

 

[AkaTrouble]

try copy the cell of one of the culprits CTRL + C

press CTRL + H to open find replace window

in the search for box paste the string /// " UPC:782113718811 ,, Height"

in that box edit it to just the /// ",,"

and in the replace box type nothing and click replace all

that way it should use the original charractor as search and replace



EDIT

i tried on your data and does not work

 

[AkaTrouble]

if you copy yout data into word it shows a linefeed

30.5 mm, Heavy-Duty Custom Pushbutton
, 10250ED1130H634
, UPC:782116322695
,
, Height: X
, Length: X
, Width: X
, Manufacturer Warranties:
, 1 year
,
, Key Code: H634
, Product Category: Keys
, Series: 10250T


i am not sure how to replace linefeed only putting this comment for others info

 

[dubmartian]

Here is also the data before I ever applied any attempt to remove line feeds and add commas in their place.

[TABLE="width: 776"]
<tbody>[TR]
[TD="class: xl65, width: 776"]10250T - Accessories

10250ED1065-2

UPC:782113718811

Dimensions:

Height: X

Length: X

Width: X

Warranties:

1 year

Specifications:

Type: 10250T

Size: 30 mm

Product Category: Adapter for standard and master indicating light
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dubmartian]

Hi Everyone, Thanks for the help. I decided to scratch the data and begin over.
What I have not was data with carriage returns / line feeds -> Substitute CHAR10) to space -> Cope and paste values -> =CLEAN function -> cope and paste values to the best I could get.
[TABLE="width: 1789"]
<tbody>[TR]
[TD="width: 1789"]10250T - Accessories 10250ED1065-2 UPC:782113718811 Dimensions: Height: X Length: X Width: X Warranties: 1 year Specifications: Type: 10250T Size: 30 mm Product Category: Adapter for standard and master indicating light Supporting documents: Eatons Volume 7-Logic Control, Operator Interface and Connectivity Solutions Eaton Specification Sheet - 10250ED1065-2

Double commas gone etc.
Thanks for your assistance in my learning...[/TD]
[/TR]
</tbody>[/TABLE]