# In Excel, how to find the value after decimal and replace entire cell with this new value?

I am looking to search a column range for 99,88,77 within a cell, which are source identifiers, for example all cells with 0.0187099 and if 99 is found to replace with a 1 been getting results 0.018701 or 0.01870.100 when i just need a whole 1 or 1.00, the 99,88 etc. will always be at the end of the cell value

-
Welcome to SuperUser. Please don't use all caps for your question. Also your question as is is not very clear. A small example might help make it better. – Brad Patton Apr 3 '13 at 2:14
0.197099 0.197099 0.197099 0.075699 1.000000 value i am looking to replace 99 with if present in cell 0.080500 – setaholic Apr 3 '13 at 2:35
So you wish to round up only values that contain 99, 88 or 77 at the end? – Kaze Apr 3 '13 at 5:23
If you lost your account cookie, try registering an account using the same email address you used to post the question. If that doesn't help, try this form. – Dennis Apr 3 '13 at 12:10

Assuming you're retaining the original returned data within a seperate column, this doesn't find the final 2 digits but it will find 99 within the cell value

``````=if(Isnumber(Search("99",CELL_containing_original_value)),1,CELL_containing_original_value)
``````

simply adjust as required. Also further conditions (for 88 and 77 etc) can be added by expanding on the false return value like...

``````=IF(ISNUMBER(SEARCH("99",G8)),1,IF(ISNUMBER(SEARCH("88",G8)),2,IF(ISNUMBER(SEARCH("77",G8)),3,G8)))
``````

probably not the best formula but hope this gets you closer to what you're after

-

Assuming the data in the relevant column is in text format and that you want to convert each entry to 1 if and only if the entry ends in any one of 77, 88 and 99 (otherwise retaining the entry from the original column) then I think the following may suit:

Since the result seems to do little more than distinguish entries with or without source identifiers conditional formatting might be more direct, say using `=OR(RIGHT(A1,2)="77",RIGHT(A1,2)="88",RIGHT(A1,2)="99")` in Format values where this formula is true: .

-