Substring function not working as expected

[ufotje]

I wrote a function to see if the value of a specific field contains a substring based on that it's suposed to do one of two things, this is the formula:

=IF(ISTEXT(SEARCH("Withdrawal for KNC*"; A7));E7+L6;L6)

, as you might be able to guess I wanna calculate all the withdrawals for KNC, E7 contains the amount L6 is the subtotal, so when the substring is found it should add E7 to L6 and write it in L7(the cell of the function), I wrote it in the first cell and then just pulled it down so it could autocomplete, the first 3 cell work great, but then I get unexpected behaviour, I was hoping someone could tell me why, online I see everyone searching for a substring with ISNUMBER instead of ISTEXT, but when I try that is returns false even when the substring is pressent.
These are some values to show what i mean with unexpected behaviour

Action (Column A) (this is the field I search for the substring)	Date	Time	Start Balance	Amount (Column E) (this is the amount to be added)	New Balance	Description	Total Withdrawals KNC (Column L)
Deposit for KNC on Kucoin	21 OCTOBER 2020	00:05:18	13000	14488,55404	27488,55404	Buy	0
Withdrawal for BTC on Kucoin	21 OCTOBER 2020	00:05:18	3	-1	2	payed for KNC	0
Withdrawal for KNC on Kucoin	21 OCTOBER 2020	00:05:18	27488,55404	-14,48855404	27474,06549	TakerFee KNC	-14,48855404
Withdrawal for KNC on Kucoin	21 OCTOBER 2020	00:05:18	27474,06549	-2,84	27471,22549	WithdrawFee KNC	-17,32855404
Withdrawal for KNC on Kucoin	21 OCTOBER 2020	00:05:18	27471,22549	-14471,22549	13000	Transfer to Binance	-14488,55404
Deposit for KNC on Binance	21 OCTOBER 2020	00:05:18	13000	14471,22549	27471,22549	Transfer from Kucoin	-17,32855404 (unexpected value should be -14488,55404)

 

[Joe4]

Welcome to the Board!

why, online I see everyone searching for a substring with ISNUMBER instead of ISTEXT,

You want to use ISNUMBER, not ISTEXT. It does not matter what you are searching for (whether it is text or number), the SEARCH function returns a number, specifically tha position within the string that the substring you are looking for starts at. So if it finds it, it will return a number. If it does not find it, it will return an error. That is why you want to use ISNUMBER.

 

[ufotje]

Welcome to the Board!


You want to use ISNUMBER, not ISTEXT. It does not matter what you are searching for (whether it is text or number), the SEARCH function returns a number, specifically tha position within the string that the substring you are looking for starts at. So if it finds it, it will return a number. If it does not find it, it will return an error. That is why you want to use ISNUMBER.

As I said in my first post, when I use IsNumber, it always return false, even when the text is part of the string

 

[Joe4]

First off, get rid of the asterisk. There is no need to have that when searching for a substring.

If I have this in cell A7:

Withdrawal for KNC on Kucoin

Then If I enter this formula:

=SEARCH("Withdrawal for KNC";A7)

it returns "1" for me.
Does it not do this for you?

 

[ufotje]

I didn't try =Search(...), I tried IF(IsNUMBER(SEARCH(...))...) and that didn't work

 

[Joe4]

I didn't try =Search(...), I tried IF(IsNUMBER(SEARCH(...))...) and that didn't work

The way to debug these things is to start from the inner most single function, and then work your way out, one function at a time.
We start with just SEARCH first, and then when that is working we can add the next step. (ISNUMBER).

So can you please try that and tell us what happens?

 

[ufotje]

if i remove the asterix the function dosn't work eather

 

[Joe4]

Please try what I asked you up in post 4 and tell me EXACTLY what the formula returns.

 

[ufotje]

When I use seracht("", A4) it returns the substring starting from the second character

 

[ufotje]

not the substring but the whole string;, when I then change it =ISNUMBER(SEARCH("With...", A7)) I get false