Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
3  
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? –  Ellesa 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

2 Answers 2

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

share|improve this answer

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:

SU577131 example


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: .

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.