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

 

[levipe]

Try something like this:

 =--(LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,2))

 

[Tetra201]

To get numeric values, try

=SUBSTITUTE(A2,",","")/100

To get text values, try

=TEXT(SUBSTITUTE(A2,",","")/100,"$#,##0.00")

 

[JuicyMusic]

=TEXT(SUBSTITUTE(A2,",","")/100,"$#,##0.00")

This one didn't work. Here is one example of a result. export: 328.75 formula results: $3.29

 

[JuicyMusic]

=--(LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,2))

Hi, I tried your formula. Here are the results. Export: 150 (for one hundred fifty dollars) Formula results: 0.50
Let me know. TYSM

 

[Tetra201]

See if the following updated formula works for you:

=IFERROR(--A2,SUBSTITUTE(A2,",","")/100)

 

[Peter_SSs]

See if the following updated formula works for you:

=IFERROR(--A2,SUBSTITUTE(A2,",","")/100)

@Tetra201
That doesn't work for the OP's first example (15:88)

sometimes the decimal is a : example for this is the first row below.

I came up with these much longer possibilities depending on whether a numerical value is required (col B) or text value (col C)

23 07 04.xlsm
	A	B	C
1	15:88	15.88	$15.88
2	174,18	174.18	$174.18
3	5,75	5.75	$5.75
4	1,000,00	1000	$1,000.00
5	236,11	236.11	$236.11
6	339,44	339.44	$339.44
7	1,023,62	1023.62	$1,023.62
8	150	150	$150.00
9	328.75	328.75	$328.75
10	$1,555,00	1555	$1,555.00
11	5	5	$5.00
Convert

Cell Formulas
Range	Formula
B1:B11	B1	=LET(t,SUBSTITUTE("00"&A1,"$",""),p,LEN(t)-2,c,MID(t,p,1),--SUBSTITUTE(LEFT(t,p-1)&IF(OR(c={":",","}),".",c)&RIGHT(t,2),",",""))
C1:C11	C1	=LET(t,SUBSTITUTE("00"&A1,"$",""),p,LEN(t)-2,c,MID(t,p,1),TEXT(SUBSTITUTE(LEFT(t,p-1)&IF(OR(c={":",","}),".",c)&RIGHT(t,2),",",""),"$#,##0.00"))

 

[Tetra201]

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

Here is a remedy:

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

 

[Peter_SSs]

That's a lot simpler than mine.

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

 

[JuicyMusic]

See if the following updated formula works for you:

=IFERROR(--A2,SUBSTITUTE(A2,",","")/100)

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