Excel Question

Factual Questions
1

I am looking at the detail of my daily energy usage which I van copy from the supplier’s web portal.

The problem is that they have entered all the usage as numbers followed by kwh.

|86.86 kWh|
|75.77 kWh|
|67.37 kWh|

Is there a simple way to strip “kwh” from the table so I just have the number?

2

Two easy ways.

  1. Do a find and replace to replace " kWh" with nothing.
  2. Under the “Data” tab, select “Text to Columns”, then “delimited”, then check “Space”. That will separate the “kWh” into it’s own column.
3

That’s what I was going to suggest. Not a real “Excel solution” but it should work fine. I’m not sure if Excel actually has a replace function. If not, copy and paste into Word and do it there.

4

Find and replace work fine. So obvious really :blush:

5

@AlsoNamedBort’s second method (to convert “Text to Columns” under the Data tab and select spaces as the delimiter with “Treat consecutive delimiters as one” checked) is the correct and robust way to do this operation. The “find and replace” method will work provided that the formatting is completely consistent for each cell but it is fragile because if you are using cut & paste from a webpage or a PDF that consistency is not guaranteed. It is better to learn the correct (i.e more robust) way of doing things rather than the expedient rote method that fails on you when everything is not just so. This method will also work for longer lines of data or ones where the units may differ.

Stranger

6

You can also use mid and find:

Let’s say your 87 kw or 137 kwh or whatever is in Cell A1

Define B1 as =MID(A1,1,FIND(" ",A1))

Fill down to make column B convert all of Column A

7

All the find and replace and MID or LEFT options above are fine … with one issue.
The formula result is text, not a number so you can’t (readily) total them or run numeric formulas e.g. multiply by the billing rate to show the daily cost.

On the assumption that all the cell entries are in the generic format: 999.99 kWh
a formula to do what you are looking for is: =VALUE(LEFT(A1,FIND(“k”,A1)-1))

There are other similar constructions.

8

Thanks guys - I got it now