only compare numbers in cells

[mcb7722]

I need to compare two cells, from different sheets. They say the same thing, but for some reason the person who wrote them added a space in somewhere so the two cells do not match up.

ex. (underscores are spaces)

"PSGA-DOC-42786_ver_1.0_(ILC)" <> "_PSGA-DOC-42786_ver_1.0_(ILC)_"

The spaces are making the values unequal, so its impossible to run the code I've written correctly.

Is there any way to compare just the numbers (4278610=4278610)

 

[Lewiy]

Try using TRIM(A1) which will remove leading or trailing spaces and double spaces, or SUBSTITUTE(A1," ","") which will remove all spaces.

 

[Aladin Akyurek]

Run the free ASAP Utilities on the data to remove errant spaces...

 

[dave3009]

Hi There

You could try TRIMming the data to remove trailing spaces like

=TRIM(A1)=TRIM(A2)

Give that A1 and A2 are the cells in question

Or perhaps just removing all spaces all together with

=SUBSTITUTE(A1, " ", "")=SUBSTITUTE(A2, " ", "")

Both these formulas will return TRUE or FALSE

HTH


Dave

 

[Scott Huish]

Is there any way to compare just the numbers (4278610=4278610)

IF you download and install the free MOREFUNC add-in available here:
http://xcell05.free.fr/

then:

=REGEX.SUBSTITUTE(A1,"\D","")=REGEX.SUBSTITUTE(B1,"\D","")

Book1
	A	B	C	D
1	PSGA-DOC-42786_ver_1.0_(ILC)	PSGA-DOC-42786_ver_1.0_(ILC)	TRUE
2	PSGA-DOC-42786_ver_1.2_(ILC)	PSGA-DOC-42786_ver_1.0_(ILC)	FALSE
Sheet1