only compare numbers in cells

mcb7722

New Member
Joined
Jul 5, 2007
Messages
14
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)
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try using TRIM(A1) which will remove leading or trailing spaces and double spaces, or SUBSTITUTE(A1," ","") which will remove all spaces.
 
Upvote 0
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
 
Upvote 0
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
ABCD
1PSGA-DOC-42786_ver_1.0_(ILC)PSGA-DOC-42786_ver_1.0_(ILC)TRUE
2PSGA-DOC-42786_ver_1.2_(ILC)PSGA-DOC-42786_ver_1.0_(ILC)FALSE
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top