Comparing static value with value derived from a formula not working....

hikerguy

New Member
Joined
Sep 11, 2014
Messages
13
Here's the situation:

Let's say I have an IP address in cell E4 of 10.234.8.11. I use a formula to extract the 2nd and 3rd octets, and place that result in F4:

=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100)) ----> result is 234.8 in cell F4

I also some data arranged like this (in two columns):

STL 234.8
OMJ 236.7
CHI 238.9

Let's say for STL, the value 234.8 is in cell C4. My goal is to create a formula in cell G10 (an IF statement) to see if F4 matches C4. If it matches, place "textA" in G10l. If it doesn't match, place "textB" in G10.

I've formatted F4 and C4 as text.

I can physically see that the result of the formula in F4 does in fact match C4, yet if I do this as a test:

=IF(C4=F4,"yes","no")

It comes out "no" (when I can clearly see they ARE identical).

Yet if I do this:

=EXACT(C4,F4)

it comes out TRUE.

What the heck is going on????? Should I be doing something else to say "Compare the result of the formula in F4 with C4? It just makes no sense the the EXACT command gives me the result I'd expect, but the IF formula doesn't.

Thanks,

Andy
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
Hi Hikerguy,

You're comparing numeric to tex (the formatting won't actually change that) so you've two choices:
1. Change the F4 to numeric by adding +0 at the end of the TRIM
2. Change the numeric in C4 to text

Book1
BCDEFG
4STL234.810.234.8.11234.8TextA
5OMJ236.710.236.7.11236.7TextA
6CHI238.910.238.9.11238.9TextA
Sheet1
Cell Formulas
RangeFormula
F4:F6F4=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100))+0
G4:G6G4=IF(C4=F4,"TextA","TextB")


or

Book1
BCDEFG
4STL234.8234.810.234.8.11234.8TextA
5OMJ236.7236.710.236.7.11236.7TextA
6CHI238.9238.910.238.9.11238.9TextA
Sheet1 (2)
Cell Formulas
RangeFormula
F4:F6F4=TRIM(LEFT(SUBSTITUTE(REPLACE(E4,1,FIND(".",E4),""),".",REPT(" ",100),2),100))
G4:G6G4=IF(D4=F4,"TextA","TextB")
D4:D6D4=TEXT(C4,"###.#")
 

hikerguy

New Member
Joined
Sep 11, 2014
Messages
13
Thanks for that info toadstool. Since posting this, I discovered that if I formatted both cells to General, then Excel saw them as equal values. But I'm keeping your info as well in case I run into other issues not solved by switching to General.

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top