How to remove a second and/or third comma

[woutv]

Hello

I have a big data file in excel with some 'weird' numbers
for example:
[TABLE="width: 120"]
<tbody>[TR]
[TD="width: 120"]4959,68,4611,9[/TD]
[/TR]
</tbody>[/TABLE]
Sometimes it's like this: 7500,7152,22
How can i remove the last commas so that only the first comma will stay?

Thanks in advance
Regards
Wout
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Ho[TABLE="width: 120"]
<tbody>[TR]
[TD="width: 120"]4959,68,4611,9

[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[tyija1995]

Hey,

try this:
=SUBSTITUTE(SUBSTITUTE(A1,",","",2),",","",2)

Where A1 contains the number

 

[nemmi69]

You can get Excel to open a data file as comma delimited or the following may help https://www.mrexcel.com/forum/excel...-multiple-rows.html?highlight=comma+delimited

 

[woutv]

Hey @tyija1995

This doesn't work, i get an error when i press enter.

 

[DanteAmor]

If you have 1,2,3, 4 or more commas:

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

 

[woutv]

Ok it did work, just had to adapt some , in your formula to ;

Thank you very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[tyija1995]

@DanteAmor

Nice solution! Works very well for multiple commas, the only concern is if the text in question actually uses the pipe character "|" - if so then there will be commas added for every pipe. I am sure that this isn't a problem but just noting it as it's interesting.

 

[DanteAmor]

@DanteAmor

Nice solution! Works very well for multiple commas, the only concern is if the text in question actually uses the pipe character "|" - if so then there will be commas added for every pipe. I am sure that this isn't a problem but just noting it as it's interesting.

You're right, I used a little common character, but it can happen, to round it off, it could be a character combination: "@|" or something like that.
Thanks for the observation

 

[woutv]

Hi actually it was the formula from @tyija1995 that helped me.