SOLVED

Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2185070%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2185070%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20write%20a%20formula%20which%20checks%20if%20a%20certain%20cell%20contains%20x%20word.%20This%20word%20is%20automatically%20filled%20in%20via%20a%20Microsoft%20Power%20Automate%20flow.%20Which%20adds%20a%20row%20every%20time%20the%20connected%20Microsoft%20Form%20is%20filled%20in.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20the%20following%20formula%26nbsp%3B%3DSUM(IF(AB2%3AAB2%3D%22besteld%22%3B1%3B0)).%20The%20problem%20I'm%20trying%20to%20solve%20is%20when%20people%20fill%20the%20form%20out%20on%20their%20phone%20they%20sometimes%20add%20a%20space%20after%20the%20word.%20As%20in%20%22besteld%20%22.%20When%20this%20happens%20my%20current%20formula%20doesn't%20add%20a%20%221%22%20in%20the%20used%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20solve%20this%3F%20As%20in%20a%20different%20formula%20etc..%20Let%20me%20know!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2185070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi, 

 

I'm trying to write a formula which checks if a certain cell contains x word. This word is automatically filled in via a Microsoft Power Automate flow. Which adds a row every time the connected Microsoft Form is filled in. 

 

I currently have the following formula =SUM(IF(AB2:AB2="besteld";1;0)). The problem I'm trying to solve is when people fill the form out on their phone they sometimes add a space after the word. As in "besteld ". When this happens my current formula doesn't add a "1" in the used cell. 

 

Does anyone know how to solve this? As in a different formula etc.. Let me know!

3 Replies

@MartVersteeg 

As variant

=1*(TRIM(AB2)="besteld")
best response confirmed by MartVersteeg (New Contributor)
Solution

@MartVersteeg 

Try this formula:

 

=IF(ISNUMBER(SEARCH("besteld";AB2));1;0)

 

(There is no need to use SUM or AB2:AB2 here)

This works! Thanks for helping! Didn't know I don't need to use the SUM, still learning the ropes of excel formulas : )