this formulas is in English ... what is it in German?

[Andrewsan]

Hi Everyone

my laptop uses Excel 2003 English ... and my desktop Excel 2003 German.

This formula goes in cell b1 ...
it counts the number of times the character '@' appears in cell a1 ...

=len(a1)-len(substitute(a1,"@",""))

my question: how to write this formula in German for Excel 2003?

this is what I have tried so far:

=LÄNGE(A2)-LÄNGE(ERSETZEN(A2;"@";"";""))

unfortunately Excel doesn't like this and throws up the #WERT error message.

Can anyone help with a correction / alternative that will achieve the same
objective?

cheers,
Andrew

 

[Bob Phillips]

Try

=LÄNGE(A2)-LÄNGE(WECHSELN(A2;"@";"";""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron de Bruin]

hi Andrew

Why not open the workbook you create on your English laptop on your German Desktop
Excel will tranlate it for you then

 

[Bob Phillips]

or enter it in English in the immediate window as an activecell.value

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrewsan]

hi Bob

many thanks ... taht works fine

cheers
Andrew

 

[Andrewsan]

Hi Ron

Aha! neat trick ... didn't know it could that
thanks

cheers,
Andrew

 

[Andrewsan]

hallo Bob

I'm not sure I understand that comment ...
can you say it again with different words, please?

I tried looked for activecell.value in Excel help, but found no reference

cheers
Andrew

 

[Bob Phillips]

If you go into the VBIDE (ALt-F11), and ensure that the immediate window is
open (Ctrl-G), and in there assign the formula to the activecell, in English
(always in English), like so

Activecell.Value = "=len(a1)-len(substitute(a1,""@"",""""))"

If you then go back to Excel (Alt-F11), the formula will be in the local
language, complete translation.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrewsan]

Got it ... Thanks, Bob !

cheers
Andrew