Calculation of cells with string value

Sektor

Well-known Member

Joined

May 6, 2011

Messages

2,929

Office Version

1. 365

Platform

1. Windows

• Jun 19, 2011

• #2

A1 = 3.7kw
A2 = 5.5kw

The sum is: =LEFT(A1,LEN(A1)-2)+LEFT(A2,LEN(A2)-2)

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Jun 19, 2011

• #3

The approach I would use is to format the cells with a custom number format 0.0"kw" so that the number 2.3 would be displayed as 2.3kw, then conversion would be unneeded.

However if you are stuck with working with a string you could use
=left(a1,len(a1)-2)+0 to convert from the string "34.3kw" to the number 34.3

 

Upvote 0

P

PANKAJUTEKAR

Board Regular

Joined

Jun 10, 2011

Messages

79

• Jun 19, 2011

• #4

Thank You Sir for your kind reply.

But sir, how do i do this in vba coding?..........for multiple cells

 

Last edited: Jun 19, 2011

Upvote 0

Sektor

Well-known Member

Joined

May 6, 2011

Messages

2,929

Office Version

1. 365

Platform

1. Windows

• Jun 19, 2011

• #5

UDF. Summing.

Code:

Function SumKW(rng As Range) As Double

    Dim arr As Variant, i As Integer, res As Double
    
    arr = rng
    For i = LBound(arr, 1) To UBound(arr, 1)
        res = res + Left(arr(i, 1), Len(arr(i, 1)) - 2)
    Next
    
    SumKW = res

End Function

 

Last edited: Jun 20, 2011

Upvote 0

P

PANKAJUTEKAR

Board Regular

Joined

Jun 10, 2011

Messages

79

• Jun 20, 2011

• #6

Thank You Very much sir.

 

Upvote 0

P

PANKAJUTEKAR

Board Regular

Joined

Jun 10, 2011

Messages

79

• Jun 20, 2011

• #7

Dear Sir,

One more problem is there -
a listbox contain some values.

"Private Sub Worksheet_Activate()" i added that items on worksheet active, also i was tryed on worksheet open also.
but the value of listbox not showing when i am opened the sheet newly.
i have to go to in coding part and do "alt - f11" then only they displayed.

So sir is there other for this?

 

Upvote 0

Sektor

Well-known Member

Joined

May 6, 2011

Messages

2,929

Office Version

1. 365

Platform

1. Windows

• Jun 20, 2011

• #8

Improved version. Can take contigous and non-contigous cells.

Code:

Function SumKW(ParamArray arr() As Variant) As Double

    Application.Volatile
    
    Dim i As Integer, res As Double
    Dim cell As Range
    
    For i = LBound(arr) To UBound(arr)
        For Each cell In arr(i)
            res = res + Left(cell, Len(cell) - 2)
        Next
    Next
    
    SumKW = res

End Function

 

Upvote 0

P

PANKAJUTEKAR

Board Regular

Joined

Jun 10, 2011

Messages

79

• Jun 20, 2011

• #9

But Sir, how do i select the prpoer cell where i need the total sum.
suppose cells are b67:b112 this total sum display in b115.
This b115 i cant able to recognize.

can u please suggest for the above?

 

Upvote 0

Sektor

Well-known Member

Joined

May 6, 2011

Messages

2,929

Office Version

1. 365

Platform

1. Windows

• Jun 20, 2011

• #10

Type in B115: =SumKW(B67:B112)

If you want to add non-contigous ranges, type in:
=SumKW(B67:B112,B55:B60,B40)

 

Upvote 0