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

  • Thread starter Thread starter Karen
  • Start date Start date
K

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?
 
Are all of the numbers currency? meaning will there always be a decime
with two digits after it
 
one way:

=IF(RIGHT(A1,1)="-", -1, 1) * SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1, ".", "$", LEN(A1) - LEN(SUBSTITUTE(A1, ".", ""))), "-",
""), ".", ""), "$", ".")
 
Karen said:
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)
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top