Close gap in numbers, sum and total

[Clemo1]

Hi Guys,

I have a spreadsheet that i have copied from an email which includes prices for certain equipment. I would like to remove the gap between the £ sign and value to neaten up but also due to me copying into excel it will not let me sum total or add any further formulas to turn it into a working sheet (yellow columns). I have had a read up and think that due to copying from an email it may be that it is all in text, hence causing issues with formulas etc.

Thank you.

 

[Rick Rothstein]

Select all of those cells and change the Cell Format from Accounting (what it is now) to Currency.

 

[sandy666]

or

Sale (£)	Hire (£)
38.82	0.21

 

[Clemo1]

Select all of those cells and change the Cell Format from Accounting (what it is now) to Currency.

Hi Rick. It is in currency already.

 

[Clemo1]

or

Sale (£)	Hire (£)
38.82	0.21

Hi Sandy666

Thanks for the reply but not sure what to do here. I am a bit of a novice with this stuff.

 

[Rick Rothstein]

Hi Rick. It is in currency already.

If the cells are formatted as Currency but displaying that way, then the values themselves are probably text. (Untested before going to sleep) Try selecting the cells and use Excel's Replace dialog (CTRL+H) to replace the currency symbol with nothing and then put a single space in the Find What field and replace that with nothing as well.

 

[sandy666]

but not sure what to do here. I am a bit of a novice with this stuff.

Sale (£)	Hire (£)	Format
38.82	0.21	Number
€ 38.82	€ 0.21	Accounting
€38.82	€0.21	Currency

imho, repeating the £ symbol in every cell is not necessary, it is enough if it is in the header.
unless you have different currencies in the same column

btw. I don't use £ but €

 

[Clemo1]

Sale (£)	Hire (£)	Format
38.82	0.21	Number
€ 38.82	€ 0.21	Accounting
€38.82	€0.21	Currency

imho, repeating the £ symbol in every cell is not necessary, it is enough if it is in the header.
unless you have different currencies in the same column

btw. I don't use £ but €

Thanks. Yea i got that but my issue is that it will not let me copy, and paste into any other format than the text accounting that is taken from the email. I have tried the replace dialog and still cannot get it to work.

 

[Clemo1]

If the cells are formatted as Currency but displaying that way, then the values themselves are probably text. (Untested before going to sleep) Try selecting the cells and use Excel's Replace dialog (CTRL+H) to replace the currency symbol with nothing and then put a single space in the Find What field and replace that with nothing as well.

Hi Rick,

Thnaks for this. I tried the replace dialog before i posted but cannot get it to work. Never mind, may just need to input manually!

 

[sandy666]

you can try

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

then change format to Currency

Book1
	A	B	C	D
1	£ 38.82	£ 0.21	£38.82	£0.21
Sheet2

Cell Formulas
Range	Formula
C1:D1	C1	=--TRIM(SUBSTITUTE(A1,"£"," "))

if values are as text (copied from email)