Substitute function in Excel

**bjnockle** · 05-07-2013, 07:20 PM

I have a formula that needs to be modified to remove spaces if there are any between the words. The target text value is “Almond Papaya Papaya” in column A. There are scenarios where the target value has a single space (Almond Papaya Papaya), double (Almond Papaya Papaya), triple (Almond Papaya Papaya) and no space (AlmondPapayaPapaya).

I would like this formula to capture those scenarios, and I believe that the Substitute function in Excel is capable of removing these spaces without changing the formula’s output.

Here is the formula I have:
=IF(AND(A2="Almond Papaya Papaya",B2="Home"),"Yes",IF(AND(A2=" Almond Papaya Papaya",B2="Key"),"Yes",IF(AND(A2=" Almond Papaya Papaya ",B2="Door"),"Yes","")))

Thanks for your assistance in advance.

**FDibbins** · 05-07-2013, 07:33 PM

Hi and welcome to the forum

Im not sure if there are any extra spaces in

single space (Almond Papaya Papaya), double (Almond Papaya Papaya), triple (Almond Papaya Papaya)

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**daddylonglegs** · 05-07-2013, 07:48 PM

I think you could use this version

=IF(AND(SUBSTITUTE(A2," ","")="AlmondPapayaPapaya",OR(B2={"Home","Key","Door"})),"Yes","")

**bjnockle** · 05-07-2013, 07:58 PM

FDibbins: Thanks for the reply. I have attached a sample copy of the formula. In the sample file, the formula is returning "Empty cell" for A2, A4 and A5 because of the spaces in these cells. A2 has no spaces (AlmondPapayaPapaya), A4 has double spaces (Almond Papaya Papaya) and A5 has three spaces (Almond Papaya Papaya). The formula is returning empty cell for these cells because it is hard coded for only single space. Substitute function should work what I am looking for.

Thanks for your help in advance

**daddylonglegs** · 05-07-2013, 08:06 PM

Did you try the formula I suggested? Of course because you are doing the comparison with spaces removed you could also get a match with an entry like "al Mondpap ayapa paya"