CODEWITHSUNDEEP
excel-formulaexcel-2016
Paul
Paul

Reputation: 5

MS Excel - Removing All Spaces then comparing the two cells

I'm trying to keep the code to just one cell. I want to remove all the spaces and then compare the left 5 characters to another cell.

I have this

=LEFT(A1,5)=LEFT(B1,5)

And I have this

=SUBSTITUTE(A1," ", "")

I want to combine the two but can't get it right.

=SUBSTITUTE(A1," ","")*LEFT(A1,5)=SUBSTITUTE(B1," ","")LEFT(B1,5)

Upvotes: 0

Views: 1525

Answers (2)

TFrazee
TFrazee

Reputation: 807

Try using the return value of the SUBSTITUTE function as the input for the LEFT function.

=LEFT(SUBSTITUTE(A1," ", ""),5)=LEFT(SUBSTITUTE(B1," ",""),5)

What this does is that it removes the spaces from the value of the cell before it puts it through the LEFT function.

Upvotes: 1

OverflowStacker
OverflowStacker

Reputation: 1338

you mean it like this?

=LEFT(SUBSTITUTE(A1," ",""),5)=LEFT(SUBSTITUTE(B1," ",""),5)

Upvotes: 0

Related Questions