Need formula for substracting the number “9” to each number inside a long number and more.

I want you to have some fun. I need something specific.

First i must explain what i do. I use a simple codification for product prices at retail store, because i dont want people know the real price for themselves. So i change the original numbers to another substracting the number 9 for each number. Normally I manually write down all the prices with this codification for every product.

So.. for example number 10 would be 89. (9-1 = 8) and (9-0 = 9) Other examples:

$128 = 871
$75 = 24
$236 = 763
$9 = 0
Finally i put 2 number nines (9) at the beginning of the codified price also, to confuse people who might think that number could be the price.

So the examples i used before are like this:

99871 (means $128)
9924 (means $75)
99763 (means $236)
990 (means $9)
Remember that i need 2 (two) nines before the real price. The real prices never start with 0 so, the nines are the begginning are only for confuse people.

Ok. So, now that you understand, here comes the 2nd part. I have an excel whith hundreds of my products added, with prices, description, etc. And i decided it is time to use a printer and start to print this information from excel. I have a software to do that, but first i need to have the codified prices in the excel also.

The fun part begins when i want to convert the real prices that are already written in my excel document into a new column AUTOMATICALLY. So that way i don΄t have to type again all the prices in codified form for the old and new items i add in the future.

Can someone help me with this? Is it even possible?

I tried with =A1-9999 but, it works well with 2 character number only. Because if the real price is 5, i will get 3 nines: 9994(code). And if the price is 234 i will get only 1 nine 9765(code). And it is a condition i need to have the TWO nines at first.

Thank you very much in advanced!

@Phuocam
Thanks for your answer.
First, i didnt know where exactly put that, but i tried using an empty sheet, pasting the formula in any cell and gives me an error.

@ TMS
Thank you!

@Phuocam
I know this is an excel forum, but i did a test right now. And i uploaded the file to google spreadsheets, because its useful to me to have it there also.
It works but, there is a small difference in the results. What happen is that in the converted number it shows 6 nines at the beginning instead of just 2.
Maybe there is a simple solution for it and you know it? Or i just have to go to a google forum.
Thanks!

Edit formula in B2 post #4

="99"&10^LEN(INT(A2))-1-A2

in C2:

=10^(LEN(INT(B2))-2)-1-RIGHT(B2,LEN(B2)-2)

Hope this helps!

This is the formula I came up with and posted over in my home forum (MrExcel) where the OP cross-posted his question to (I sure hope he reads FDibbins' message in Post #10)....

=IFERROR(0+(99&REPT(9,LEN(A1))-A1),"")

Note that this formula outputs the empty string ("") for non-numeric values in the referenced cells.

I am really sorry guys. I should have read the rules first, but i didn΄t.
As FDibbins said, i posted the same question in other forum. (I didnt know there was the same people in both places, and also didnt expect feedback this fast.

So here is the link for the same question in other forums:
http://www.mrexcel.com/forum/excel-q...mber-more.html

I am aware now.

I will try all possible formulas and solution and then i will answer which worked better for me. Until now i tried Phuocam xls in excel and it worked great!

Thanks to everyone!

You're welcome, good luck!

@Phuocam
Im sorry to bother you again but i realize there is a small issue with the formula.
It doesn΄t well when the price numbers start with ninety or ninehundred something.. for example 90, or 98 or 904.
In those cases it only converts the numbers after the 9.

So, when the real price is 90 it converts to 999, when it should be 9909.
Or when the price is 904 it converts it to 9995 when it should be 99095.

It seems it has something to do with the nine not being convert to zero number.

Do you know what could be the reason?

Yeah! It worked.

I really, REALLy, appreciate all your help.

Again.. thank you very much!

You're welcome!