0

I am trying to work out how to add up a list of cells that all contain alphanumeric values. For example, if the cell data is 3.098 GB (3,326,698,574 bytes)

I am using the below to strip out the brackets and letters:

=LEFT(G55,LEN(G55)-7)
=RIGHT(C55,LEN(C55)-10)

Which will get me to something like: 3,326,698,574.

What I would like to do is then add all the cells using =sum, but I can't get it to work.

2 Answers 2

1

You can get the number with

=VALUE(MID(LEFT(G55,LEN(G55)-7),FIND("(",G55)+1,99))

If this leaves you with a #VALUE error, your Excel doesn't automatically handle the commas inside your text. If that's the case, add a substitute:

=VALUE(SUBSTITUTE(MID(LEFT(G55,LEN(G55)-7),FIND("(",G55)+1,99),",","")

Let's say this value is in C55. You can then convert this to your original notation as follows:

=ROUND(SUM(C55)/1024^3,3)&" GB ("&TEXT(SUM(C55),"#,###")&" bytes)"

You can replace C55 with the range of your data.

1

You could replace the C55 reference in the second formula with the C55 formula to reduce this to one formula. However, your result is text, which doesn't sum, and your formula will work only for values in that size range. Here is a way to build a generic formula that will work for any size values and give you numeric results. I'll build it a step at a time so the action is clearer.

You always want to work with the full number that is inside the parentheses. To locate that regardless of the size of the numbers, you can look between the open parenthesis "(" and the subsequent space. The location of the "(" is:

    FIND("(",G55)

Finding the subsequent space starts the search at that location, so its location is:

    FIND(" ",G55,FIND("(",G55))

You want to extract your number from the middle of a string, so use the MID function:

    MID(G55,FIND("(",G55)+1,FIND(" ",G55,FIND("(",G55))-FIND("(",G55)-1)

That extracts from the string in G55, starting in the position after the "(", and including the number of characters in the difference between the two locations (adjusting the count for the position of the delimiter). That gives you the text version of the number. To sum the values, you need to convert it to a number:

    =VALUE(MID(G55,FIND("(",G55)+1,FIND(" ",G55,FIND("(",G55))-FIND("(",G55)-1))

Everything in the formula references cell G55 so if the values are in a column, copy the formula down your helper column to get the list of extracted numbers that you can then sum.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .