Excel data conversion

kierank01

Hi, not really sure where to put this, so here it goes...

I have an excel table that has data like below, formated as text (each cell has numbers and text).

I need to be able to add each item together, so need to be able to split/convert each item to an integer, and then normalise the units, and then add.

Any idea how to do this?

48.0 KB
4.62 MB       
48.0 KB       
4.6 MB       
48.0 KB       
839.0 KB       
48.0 KB       
468.0 KB       
48.0 KB       
2.74 MB       
48.0 KB       
8.72 MB

kierank01

By doing a little searching in the functions list, I have come up with:

=REPLACE(A1,FIND(" ",A1,1),3, )
and
=REPLACE(A1,1,FIND(" ",A1,1), )

The results of these seem to select the values and the unit, and allows the values to be converted to the same unit, and added.

don't know if there is a more elegant way to do this.

homer911

Try the attached...

=IF(RIGHT(TRIM(B4),2)="KB",VALUE(LEFT(TRIM(B4),LEN(TRIM(B4))-3))/1024,VALUE(LEFT(TRIM(B4),LEN(TRIM(B4))-3)))

meemeep

much simpler - on the data tab, there's an option called "text to colums". Just select the list of colums. If you chose delimited, then delimit by space, select destination colum (ie 2 new colums beside them) and finish - it will split them nicely.