HOW TO CONVERT 2ND COMMA IN A DOLLAR AMOUNT TO A DECIMAL

[JuicyMusic]

How do I convert the second comma into a decimal point with a formula? There is a 2nd comma in my export due to something that can't be resolved, so has to be corrected manually.

Note: Export has 92 rows with good dollar values......19 rows of the 92 have this issue.

1. Can't do a find/replace because the file is filtered.
2. Can't do find/replace because there are amounts that need to keep the 1st comma......Such as $1,555.00.....my export comes across as $1,555,00.

Here is a sample of the export data:....sometimes the decimal is a : example for this is the first row below.

15:88

174,18

5,75

1,000,00

236,11

339,44

1,023,62

 

[JuicyMusic]

That's a lot simpler than mine.

@JuicyMusic
For any future questions, please note #14 of the Forum Rules

Thank you for the reminder, but what rule did I not follow?....Let me know so I don't do going forward

 

[JuicyMusic]

Oops, I have totally missed the colon remark -- thank you Peter for catching this.

Here is a remedy:

=IFERROR(--SUBSTITUTE(A1,":","."),SUBSTITUTE(A1,",","")/100)

This formula worked perfectly as well!!.

 

[JuicyMusic]

PERFECT RESULT!!!! Thank you so so much! SOLVED

Oops, I have totally missed the colon remark -- thank you Peter for catching this.

Here is a remedy:

=IFERROR(--SUBSTITUTE(A1,":","."),SUBSTITUTE(A1,",","")/100)

Tetra201, I'm really adding an additional reply to your corrected post. Your corrected formula did work for me.....I had to add an additioanal IFERROR to have a blank space where the results were #VALUE. That works great for me but let me know what you think.

Here is how the formula looks like now.

=IFERROR(
IFERROR(--SUBSTITUTE(D41,":","."),SUBSTITUTE(D41,",","")/100),"")

 

[Peter_SSs]

but what rule did I not follow?....Let me know so I don't do going forward

I noted which rule in my post (#14) & provided a link. It includes:

... must not SHOUT (use all upper case) or use excessive amounts of punctuation (e.g. ! and ?) in posts or thread titles.

 

[JuicyMusic]

Okay, noted. Thank you so much.