Sum Numbers in a Cell that has text

[ennisb]

Could use lot's of help as I enter the world of Excel. Total novice.

I was given a sheet that has numbers and text in the same cells. I wil
need to sum up the numbers so I can produce the total storage space.
Some cell examples are listed below. I do not care at all about th
text. I tried to create a custom formula using " to escape the tex
but when I sum the fields I got 0.00 "Gb...."

Example data
The data is in column G

32.4Gb available
(raw)

32.4Gb available
(raw)


Or two set's of numbers and text in a cell. Again all I want is to su
up the 3.87gb,35.8...

3.87Gb available
(mirrored)1,2
35.8Gb available
(raw)1,3

4.16Gb available
(mirrored)1,2
35.8Gb available
(raw)1,3

Thank

 

[Frank Kabel]

Hi
one idea (if all your relevant numbers end with Gb):
- add a helper column H
- insert the following formula in H1:
=IF(ISNUMBER(FIND("Gb ",G1)),--LEFT(G1,FIND("Gb ",G1)-1),"")
copy this formula down and sum column H

 

[ennisb]

I just plugged that formula into a cell and it worked for 85% of th
cells. The few that failed either had double disk space entries or som
extra spaces.

This is great thank you so much, what's left will be a minor cleanup.


Thankyou,

Brya

 

[Frank Kabel]

Hi
you may try the following
=IF(ISNUMBER(FIND("Gb ",TRIM(G1))),--LEFT(TRIM(G1),FIND("Gb
",TRIM(G1))-1),"")