# Excel Question **URL:** \ **Category:** Factual Questions\ **Created:** [March 2, 2023, 5:11pm UTC](https://boards.straightdope.com/t/excel-question/980602 "2023-03-02T17:11:13Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![bob\_2](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bob_2/32/3341_2.png) [@bob\_2](https://boards.straightdope.com/u/bob_2)\ **Post date:** [March 2, 2023, 5:11pm UTC](https://boards.straightdope.com/t/excel-question/980602/1 "2023-03-02T17:11:13Z")
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? ---
**Author:** ![AlsoNamedBort](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/alsonamedbort/32/3213_2.png) [@AlsoNamedBort](https://boards.straightdope.com/u/AlsoNamedBort)\ **Post date:** [March 2, 2023, 5:16pm UTC](https://boards.straightdope.com/t/excel-question/980602/2 "2023-03-02T17:16:51Z")
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. ---
**Author:** ![alovem](https://avatars.discourse-cdn.com/v4/letter/a/b38774/32.png) [@alovem](https://boards.straightdope.com/u/alovem)\ **Post date:** [March 2, 2023, 5:19pm UTC](https://boards.straightdope.com/t/excel-question/980602/3 "2023-03-02T17:19:45Z")
> [@AlsoNamedBort](#): > > Do a find and replace to replace " kWh" with nothing. 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. ---
**Author:** ![bob\_2](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bob_2/32/3341_2.png) [@bob\_2](https://boards.straightdope.com/u/bob_2)\ **Post date:** [March 2, 2023, 5:20pm UTC](https://boards.straightdope.com/t/excel-question/980602/4 "2023-03-02T17:20:48Z")
Find and replace work fine. So obvious really 😊 ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 2, 2023, 5:47pm UTC](https://boards.straightdope.com/t/excel-question/980602/5 "2023-03-02T17:47:20Z")
@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 ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [March 2, 2023, 6:12pm UTC](https://boards.straightdope.com/t/excel-question/980602/6 "2023-03-02T18:12:22Z")
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 ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [March 2, 2023, 10:23pm UTC](https://boards.straightdope.com/t/excel-question/980602/7 "2023-03-02T22:23:27Z")
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. ---
**Author:** ![bob\_2](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bob_2/32/3341_2.png) [@bob\_2](https://boards.straightdope.com/u/bob_2)\ **Post date:** [March 2, 2023, 11:21pm UTC](https://boards.straightdope.com/t/excel-question/980602/8 "2023-03-02T23:21:02Z")
Thanks guys - I got it now