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

dubmartian

New Member
Joined
Dec 16, 2016
Messages
20
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.

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

<tbody>
</tbody>
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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),",")
 
Last edited:
Upvote 0
Here is some VBA code that may help

Code:
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
 
Upvote 0
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?
 
Upvote 0
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 )
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Here is also the data before I ever applied any attempt to remove line feeds and add commas in their place.

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

<tbody>
</tbody>
 
Last edited:
Upvote 0
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.
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...

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top