View Full Version : Excel question - adding two cells together that have symbols

Renmoo

24-11-2007, 11:01 PM

Dear all,

Say, cell A1 shows £50, cell B1 shows £60. I wish to have the sum be displayed in cell C1, so I typed in =A1+B1, but because of the presence of £ sign, the formula has been invalidated by Excel. What can I do about this?

Cheers :)

Advocar

24-11-2007, 11:33 PM

Hi there, I use XP Pro and have had no problem.

I put in numbers only in as you listed and added OK.

Next I entered $50 and $60 In C1 A1+B1 = $110 correct.

next I checked Formating and was set to $1,123 no decimal places etc.

I changed it to English Pound Format and added in C1 correctly.

Chanhed one cell to 2 decimal places and o problems.

Suggest you open a new sheet and type in 50 A1, 60 B1, =A1+B1 into C1. If does not work check formatting. Some times cell look blank but if you hit delete or other keys cell looks blank but does not have normal formatting. Use Clear content, right mouse button, and formatting will remain standard or what it was before you cleared contents.

I am assuming your £ symbol is text in the field rather than currency formatting?

You can use the mid and concatenate functions to get Excel to first ignore the £ symbol and then to add it back in to the sum field.

=CONCATENATE("£",MID(A1,2,10)+MID(B1,2,10))

That'll will give you an answer of £110.

HTH

Mike.

stormdragon

25-11-2007, 08:35 AM

Simply insert the raw numbers in the cell's then format cells to currency and select the pound symbol.

There seems to be no need from what you've posted to complicate things with concatenate's.

andrew93

25-11-2007, 12:27 PM

Hi

All useful suggestions so far. It sounds like the pound symbol is text rather than a currency format. One way of actually removing this character is to use the Substitute function, like this:

=SUBSTITUTE(A1,"£","")

Andrew

Renmoo

25-11-2007, 06:28 PM

I am assuming your £ symbol is text in the field rather than currency formatting?

You can use the mid and concatenate functions to get Excel to first ignore the £ symbol and then to add it back in to the sum field.

=CONCATENATE("£",MID(A1,2,10)+MID(B1,2,10))

That'll will give you an answer of £110.

HTH

Mike.

Hi

All useful suggestions so far. It sounds like the pound symbol is text rather than a currency format. One way of actually removing this character is to use the Substitute function, like this:

=SUBSTITUTE(A1,"£","")

Andrew

Hi Mike and Andrew. Could you please explain how the two formulae work (e.g. first "xxx" is to specify xyz cell etc.)

Anyway, both tricks work! :p

andrew93

25-11-2007, 06:46 PM

Hi Jamuz

The substitute function that I recommended has 3 parts between the brackets, the first part is either the text you want to manipulate or a reference to a cell that contains text, the second part between the quotes is the part you want to swap (i.e. the pound symbol) and the third part is what you want to replace it with (in this case nothing, so you use two quote symbols). So all it is doing is substituting the pound symbol for nothing. If you then tried to use a mathematical function with the result it would work because the substitute function would return a value.

Mike's suggestion also involves text manipulation (I will let Mike explain the functions he used) but the crux of the problem is that the values you have are not actually stored as numbers, they have been stored as text because of the pound symbol. Once the pound symbol has been removed (per Mike's and my suggestions) then you can use mathematical functions on the remaining value.

HTH, Andrew

Renmoo

25-11-2007, 07:22 PM

Ah yeap, thanks a bunch! :thumbs:

Helplesss

26-11-2007, 12:12 PM

I am assuming your £ symbol is text in the field rather than currency formatting?

You can use the mid and concatenate functions to get Excel to first ignore the £ symbol and then to add it back in to the sum field.

=CONCATENATE("£",MID(A1,2,10)+MID(B1,2,10))

That'll will give you an answer of £110.

HTH

Mike.

I'm lazy at typing so would type the above formula thus...

="£"&(MID(A1,2,10)+MID(B1,2,10))

The & substitutes for concatenate.

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.