Close gap in numbers, sum and total

Clemo1

New Member
Joined
Feb 23, 2012
Messages
35
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.
1602663994549.png


Thank you.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,878
Office Version
  1. 2010
Platform
  1. Windows
Select all of those cells and change the Cell Format from Accounting (what it is now) to Currency.
 

Clemo1

New Member
Joined
Feb 23, 2012
Messages
35

ADVERTISEMENT

or
Sale (£)Hire (£)
38.820.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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,878
Office Version
  1. 2010
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

but not sure what to do here. I am a bit of a novice with this stuff.
Sale (£)Hire (£)Format
38.820.21Number
€ 38.82€ 0.21Accounting
€38.82€0.21Currency

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

New Member
Joined
Feb 23, 2012
Messages
35
Sale (£)Hire (£)Format
38.820.21Number
€ 38.82€ 0.21Accounting
€38.82€0.21Currency

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

New Member
Joined
Feb 23, 2012
Messages
35
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you can try
Excel Formula:
=--TRIM(SUBSTITUTE(A1,"£"," "))
then change format to Currency
Book1
ABCD
1£ 38.82£ 0.21£38.82£0.21
Sheet2
Cell Formulas
RangeFormula
C1:D1C1=--TRIM(SUBSTITUTE(A1,"£"," "))

if values are as text (copied from email)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,912
Members
416,142
Latest member
IODIEN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top