How can I remove first occurrence of a period in a cell?

[Karen]

Hi - I have many cells where there are several occurrences
of a period (.) which need to be removed, but not the last
occurrence. This is from a scanned document where the
comma was interpreted as a period.

e.g. 1.200.401.00-

needs to be changed to a number, i.e. -1,200,401.00 (or
-1200401.00 is fine too).

I have a formula which converts just fine as long as the
text has commas instead of periods.

So what I want to do is leave the last occurrence (showing
decimal places) and either remove the periods completely
or replace them with commas. Any ideas?

 

[jpendegraft]

Are all of the numbers currency? meaning will there always be a decime
with two digits after it

 

[JE McGimpsey]

one way:

=IF(RIGHT(A1,1)="-", -1, 1) * SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1, ".", "$", LEN(A1) - LEN(SUBSTITUTE(A1, ".", ""))), "-",
""), ".", ""), "$", ".")

 

[Harlan Grove]

Hi - I have many cells where there are several occurrences
of a period (.) which need to be removed, but not the last
occurrence. This is from a scanned document where the
comma was interpreted as a period.

e.g. 1.200.401.00-

needs to be changed to a number, i.e. -1,200,401.00 (or
-1200401.00 is fine too).

Another alternative if all cells have 2 numerals after the rightmost period.

=IF(RIGHT($A$1,1)="-","-","")&SUBSTITUTE(LEFT(A1,LEN(A1)-3),".","")
&MID($A$1,LEN(A1)-IF(RIGHT($A$1,1)="-",3,2),3)

 

[Daniel.M]

Karen,

Another alternative if numbers always have 2 digits after last period:

=IF(RIGHT(A1,1)="-",-1,1)*
SUBSTITUTE(LEFT(A1,LEN(A1)-(RIGHT(A1,1)="-")),".","")/100

Regards,

Daniel M.