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]

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

OK, that formula has all sorts of typos in it.

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

In your original formula, you used semi-colons as argument separators. Now you are using commas.
Which is it? Which one does your computer use.
Different versions of Excel around the world use different values for argument separators. The first thing you need to do is identify and understand which one your computer uses, and consistently use that one.

Then, let's test ONE single thing, and stop hopping around all over the place. Let's resolve one issue before moving to the rest.

So, what exactly does this formula return?

=SEARCH("With",A7)

 

[ufotje]

OK, that formula has all sorts of typos in it.

In your original formula, you used semi-colons as argument separators. Now you are using commas.
Which is it? Which one does your computer use.
Different versions of Excel around the world use different values for argument separators. The first thing you need to do is identify and understand which one your computer uses, and consistently use that one.

Then, let's test ONE single thing, and stop hopping around all over the place. Let's resolve one issue before moving to the rest.

So, what exactly does this formula return?

=SEARCH("With",A7)

I didn't coppy the formula from excel, so the typo's are here ont he platform, not in excel, then it would give an error, I use ;
this formula =ZOEKEN("Withdrawal for KNC*"; A4), which is a simple search, returns ithdrawal for KNC on Kucoin
when i use vind.spec, I don't really know what the english version is find.spec?, it returns a 1

 

[ufotje]

This works as expected =ALS(ISGETAL(VIND.SPEC("Withdrawal for KNC*"; A4));E4+L3;L3), the english version would be IF(ISNUMBER(FIND.SPEC(("Withdrawal for KNC*"; A4));E4+L3;L3) i guess

 

[Joe4]

Note that when you are posting questions, it is important to give us all the important detail, such as you are using a different language version of Excel (not English).
Perhaps all those functions do not always translate over perfectly into other versions.

So plesase be sure to mention that in future posts.

Also notice that there is a "Questions in Other Languages" forum here: Questions in Other Languages, where you can post questions in your own native language.

 

[Fluff]

FYI
VIND.SPEC =SEARCH
ZOEKEN=LOOKUP

Two totally different functions.

 

[ufotje]

Note that when you are posting questions, it is important to give us all the important detail, such as you are using a different language version of Excel (not English).
Perhaps all those functions do not always translate over perfectly into other versions.

So plesase be sure to mention that in future posts.

Also notice that there is a "Questions in Other Languages" forum here: Questions in Other Languages, where you can post questions in your own native language.

I noticed there is a subforum in native languages, but I expect to get a answer faster in english, that was I posted here

 

[ufotje]

FYI
VIND.SPEC =SEARCH
ZOEKEN=LOOKUP

Two totally different functions.

Lol, that makes no sense at all, not meaning what you're saying isn't true, but the translation sucks, zoeken translated to english is literally search, Lookup would be opzoeken in dutch

 

[Joe4]

I noticed there is a subforum in native languages, but I expect to get a answer faster in english, that was I posted here

Well, that is fine to do, but that means it is up to you to make sure you are translating to the equivalent function on the other side.
Otherwise, the information you are giving us is faulty, and we have the situation like we did here.

Here is a list of the 100 most common Excel function and their Dutch name equivalents: Excel Functions in Dutch | Easy-Excel.com

 

[Fluff]

One way to ensure that you get the right functions is to use a translator like this one here

Translator

The online tool for the translation of Excel formulas and functions from one language to another. Supports the replacement of argument separators, 35 languages and much more.

en.excel-translator.de

Helps to ensure there is no misunderstandings.