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.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or if whole table is copied from email, use Power Query to get proper values in each column
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome
CodeDescriptionNumberWeightSaleHire
2850014.0M METRIX STAN DARD118.84£38.83£0.21
2850053.0M METRIX STAN DARD114.37£30.23£0.16
2850102.5M METRIX STAN DARD112.13£27.85£0.15
2850152.0M METRIX STAN DARD19.9£20.45£0.11
2850201.5M METRIX STAN DARD17.55£17.78£0.09
2850251.0M METRIX STAN DARD15.43£12.45£0.07
2850300.5M METRIX STAN DARD13.19£9.79£0.05
2850354.0M METRIX STANDARD BOLTED SPIGOT119.9£45.05£0.26
2850403.0M METRIX STANDARD BOLTED SPIGOT115.42£35.45£0.19
2850452.5M METRIX STANDARD BOLTED SPIGOT113.19£34.09£0.18
2850502.0M METRIX STANDARD BOLTED SPIGOT110.95£25.62£0.14
2850551.5M METRIX STANDARD BOLTED SPIGOT18.72£24.01£0.13

all the yellow columns you can calculate in Power Query also
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

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