how to extract relevant part of a string

[bigdan]

I've got an Excel file which details the size of various folders.

The problem is right now the file says things like "28.2 MB", 75,913.4 MB" etc.

I'd like to extract just the numbers, so that I can then do a sum on them. What's the exact formula to do this? I'm guessing it's a FIND nested within a LEFT but I cant quite figure it out.

EDIT - I tried this formula "=LEFT(B23,FIND("MB",B23)-2)" but although it gave me the results I wanted, ie what looked like numbers, I cant add them up. Can someone advise?

 

[Fluff]

How about
=LEFT(A1,LEN(A1)-3)
This assumes all values end with spaceMB

 

[Rick Rothstein]

EDIT - I tried this formula "=LEFT(B23,FIND("MB",B23)-2)" but although it gave me the results I wanted, ie what looked like numbers, I cant add them up. Can someone advise?

The result of a text function call is text, even if that text looks like a number. The way to convert it to a real number is to involve it in a mathematical operation that won't change the number (add zero, multiply by plus 1, put a double minus sign (--) in front of it (which is the equivalent of multiplying by minus one twice). So, for your formula...

=0+LEFT(B23,FIND("MB",B23)-2)

 

[bigdan]

Thanks a lot guys! You're awesome.

 

[Fluff]

Glad we could help & thanks for the feedback