Help with if function to deleted characters if met criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1600706%22%20slang%3D%22en-US%22%3EHelp%20with%20if%20function%20to%20deleted%20characters%20if%20met%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20with%20a%20data%20set%20where%20have%20a%20lot%20of%20website%20links.%20The%20thing%20is%20that%20most%20of%20the%20links%20are%20personalized.%20For%20example%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fthis%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2F%20thisisatest%3C%2FA%3E%2Ftesting%3Fwvideo%3D9gpspqefxd%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3EWhat%20I%20need%20is%20to%20delete%20all%20the%20characters%20after%20the%20question%20mark%20(%3C%2FSPAN%3E%3CSTRONG%3E%3F%3C%2FSTRONG%3E%3CSPAN%3E)%20so%20that%20I%20would%20look%20like%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fthis%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2F%20thisisatest%3C%2FA%3E%2Ftesting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20already%20figured%20out%20the%20function%20to%20delete%20that%20which%20is%26nbsp%3B%3CSTRONG%3E%3DLEFT(A1%2CFIND(%22%3F%22%2CA1)-1).%26nbsp%3B%3C%2FSTRONG%3EHowever%2C%20in%20the%20list%2C%20of%20websites%2C%20there%20are%20also%20links%20that%20don't%20have%20the%20question%20mark%20(%3CSTRONG%3E%3F%3C%2FSTRONG%3E)%2C%20therefore%2C%20when%20I%20apply%20the%20formula%20to%20all%20cells%2C%20the%20ones%20that%20don't%20have%20the%20(%3CSTRONG%3E%3F%3C%2FSTRONG%3E)%20show%20me%20as%26nbsp%3B%3CSTRONG%3E%23VALUE!%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20of%20using%20an%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20function%20but%20I've%20been%20struggling%20to%20make%20it%20work.%20This%20is%20the%20formula%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(M2%3D%22%3F%22%2C%22(LEFT(M2%2CFIND(%22%3F%22%2CM2)-1))%22%2C%22M2%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20supposed%20to%20find%20in%20%3CSTRONG%3EA1%3C%2FSTRONG%3E%20the%20character%20(%3CSTRONG%3E%3F%3C%2FSTRONG%3E)%2C%20and%20after%20the%20condition%20is%20met%2C%20it%20should%20do%20the%20rest%20but%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20it%20if%20you%20can%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1600706%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600764%22%20slang%3D%22de-DE%22%3ESubject%3A%20Help%20with%20if%20function%20to%20deleted%20characters%20if%20met%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600764%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F765378%22%20target%3D%22_blank%22%3E%40RubenZh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20a%20link%20as%20info%20for%20this%20function%3C%2FP%3E%3CH1%20id%3D%22toc-hId-523637492%22%20id%3D%22toc-hId-523637492%22%3E%3CFONT%20size%3D%224%22%3ESEARCH%2C%20SEARCHB%20functions%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CA%20title%3D%22SEARCH%2C%20SEARCHB%20functions%22%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fsearch-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fsearch-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStart%20menu%20%3CBR%20%2F%3E%20Search%20and%20select%20menu%20From%20this%20menu%20you%20can%20select%20Search%20for%20cells%20%3CBR%20%2F%3E%20and%2C%20if%20necessary%2C%20change%20text%20or%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi guys,

 

I'm working with a data set where have a lot of website links. The thing is that most of the links are personalized. For example https:// thisisatest/testing?wvideo=9gpspqefxd


What I need is to delete all the characters after the question mark (
?) so that I would look like this:

https:// thisisatest/testing

 

I've already figured out the function to delete that which is =LEFT(A1,FIND("?",A1)-1). However, in the list, of websites, there are also links that don't have the question mark (?), therefore, when I apply the formula to all cells, the ones that don't have the (?) show me as #VALUE! 

 

I thought of using an IF function but I've been struggling to make it work. This is the formula:

=IF(M2="?","(LEFT(M2,FIND("?",M2)-1))","M2")

 

It is supposed to find in A1 the character (?), and after the condition is met, it should do the rest but doesn't work.

 

I appreciate it if you can help me.

 

Thank you!

 

1 Reply

@RubenZh 

 

here is a link as info for this function

SEARCH, SEARCHB functions

https://support.microsoft.com/en-gb/office/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513... 

 

or....

 

Start menu
Search and select menu
From this menu you can select Search for cells and, if necessary, change text or formulas.

 

I would be happy to find out if I could help.

 

Nikolino

I know I don't know anything (Socrates)