I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the rigtht
and len minus 4 formula and get the expected result (without the EUR). Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear, i
have set my regional settings to German.


--
with kind regards

Spike

Hello Spike
May be just format those figures as Number and not currency (as it seems).
HTH
Cordially
Pascal

"Spike" a écrit dans le message de news:
...
I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the
rigtht
and len minus 4 formula and get the expected result (without the EUR).
Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the
text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear,
i
have set my regional settings to German.


--
with kind regards

Spike

The LEFT and RIGHT functions will return a text value, so you won't be
able to add these up with SUM. Instead, multiply your formula by 1 to
convert the values into numeric, like so:

=LEFT(A1,LEN(A1)-4)*1

then your SUM formula will work. I assume you are using LEFT and not
RIGHT - you might need to use a semicolon instead of the comma.

Hope this helps.

Pete

On Jul 23, 2:12 pm, Spike wrote:
I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the rigtht
and len minus 4 formula and get the expected result (without the EUR). Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear, i
have set my regional settings to German.

--
with kind regards

Spike

Try this:

=--SUBSTITUTE(A1," EUR","")


"Spike" wrote:

I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the rigtht
and len minus 4 formula and get the expected result (without the EUR). Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear, i
have set my regional settings to German.


--
with kind regards

Spike

Many thanks multiplying by one works a treat
--
with kind regards

Spike


"Pete_UK" wrote:

The LEFT and RIGHT functions will return a text value, so you won't be
able to add these up with SUM. Instead, multiply your formula by 1 to
convert the values into numeric, like so:

=LEFT(A1,LEN(A1)-4)*1

then your SUM formula will work. I assume you are using LEFT and not
RIGHT - you might need to use a semicolon instead of the comma.

Hope this helps.

Pete

On Jul 23, 2:12 pm, Spike wrote:
I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the rigtht
and len minus 4 formula and get the expected result (without the EUR). Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear, i
have set my regional settings to German.

--
with kind regards

Spike

Now that Google is displaying some of my past posts, I'm just catching
up - thanks for the feedback.

Pete

On Jul 23, 3:30 pm, Spike wrote:
Many thanks multiplying by one works a treat
--
with kind regards

Spike



"Pete_UK" wrote:
The LEFT and RIGHT functions will return a text value, so you won't be
able to add these up with SUM. Instead, multiply your formula by 1 to
convert the values into numeric, like so:

=LEFT(A1,LEN(A1)-4)*1

then your SUM formula will work. I assume you are using LEFT and not
RIGHT - you might need to use a semicolon instead of the comma.

Hope this helps.

Pete

On Jul 23, 2:12 pm, Spike wrote:
I have a column of numbers as 2.119.939,50 EUR
i need to remove the EUR and sum the resultant numbers. So i use the rigtht
and len minus 4 formula and get the expected result (without the EUR). Then
when i try to add these up i get zero.

The only way around this is to copy and paste values only, then use the text
to columns function, then it works.

If anyone knows a more appropriate method i will be very grateful to hear, i
have set my regional settings to German.

--
with kind regards

Spike- Hide quoted text -

- Show quoted text -