Find/Replace last couple of digits on number

  • Thread starter Thread starter gardenhead
  • Start date Start date
G

gardenhead

Hello,

I'm trying to figure out if there's a way to find and replace the last
two digits on a list of invoice #s if they end with a 01 (replacing
them with nothing). Here's a sample list:

190369734601
190369734701
190369734801
64853
CM1903717092
DM902651322
I1903517714
I90351095901
I90351095902

Can't use the =Left function because the length of the invoice #s
varies. Currently I'm copying the list to a text editor that recognizes
carriage returns so I can find/replace 01<carriage return> and then
copy it back to the wksht. It'd be nice if I could find a way to work
it into a macro though. I realize that legitimate invoice numbers may
be affected but they should easily be identified by the difference in
size.

Thanks...
 
hi, try

=if(right(a2,2)="01",mid(a2,1,len(a2)-2),a2)



hth
regards from Brazil
Marcelo

"gardenhead" escreveu:
 

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

Similar Threads

Edit Replace the third digit only of a number 3
Replacing a last digit to something else 6
Losing rows that reference data from another worsheet 1
Removing the last two digits of a cell 3
Replacing last digit with Zero 2
Find & Replace: find part cell, replace whole cell 2
find and replace numeric strings in larger text strings 8
Numbers Converted to Text - Last Digit Replaced by Zero 14

Back
Top