Exact formula applied to same exact value gives different results

**johnmerlino** · 11-11-2010, 11:51 PM

Hey all,

Before I lose my head, I attached an excel spreadsheet where I apply the SAME formula to two different cells with the SAME content, yet it produces two different results. In cell A1, it produces unintended results. And in cell, A23, it produces intended results. Formulas look big because I am taking things out of context to address this situation but in reality this formula addresses multiple conditions. Nevertheless, the problem relates directly to why this formula is yielding two different results when applied to same value.

Thanks for response.

**alansidman** · 11-12-2010, 12:07 AM

In cell c1 and c23, I put the following formula =len(a1) and =len(a23) respectively and the results were 25 and 24. So the cells in column A are not exactly the same. Not sure what it is that is causing this. Do the values in Column A come from another source. I did a =trim(A1) and then got the same results. Appears that there was probably a space at the end of that cell

**johnmerlino** · 11-12-2010, 12:32 AM

I'm not sure what you're seeing. I'm trying to get the value returned in cell 23 to be returned in cell 1. Where's the space?

**johnmerlino** · 11-12-2010, 12:35 AM

I put the trim:

=TRIM(IF(AND(ISERROR(FIND(",",A1)),ISERROR(FIND("&",A1))),A1,IF(FIND(",",A1&",")<FIND("&",A1&"&"),IF(AND((LEN(SUBSTITUTE(A1,"&","",3))-LEN(SUBSTITUTE(A1,"&","",2))),(LEN(SUBSTITUTE(A1,",","",3))-LEN(SUBSTITUTE(A1,",","",2)))),TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1)),IF(ISNUMBER(FIND(",",A1,FIND(",",A1)+1)),TRIM(MID(A1,FIND("&",A1)+1,FIND("&",A1)-3)),IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),CONCATENATE(LEFT(A1,FIND(",",A1)-1),",",TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1))),IF(ISNUMBER(FIND("&",A1)),SUBSTITUTE(A1,MID(A1,FIND(",",A1)+1,FIND(" &",A1)-FIND(",",A1)+2),""),A1)))),RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))&","&LEFT(A1,FIND("&",A1&"&")-1))))

And I get the bad value to display in both of them. How do I get the right value to display in both of them?

Thanks for response.

**johnmerlino** · 11-12-2010, 01:01 AM

Actually you're right. The data did have space and removing it fixed problem.

Exact formula applied to same exact value gives different results

LinkBack

Thread Tools

Rate This Thread

Display

Exact formula applied to same exact value gives different results

Re: Exact formula applied to same exact value gives different results: proof attached

Re: Exact formula applied to same exact value gives different results: proof attached

Re: Exact formula applied to same exact value gives different results: proof attached

Re: Exact formula applied to same exact value gives different results: proof attached

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions