CODEWITHSUNDEEP
excelcellvba
haran kumar
haran kumar

Reputation: 337

Reference part of a cell in excel vba

I wanted to know a way to reference part of a value in a cell in Excel in VBA. For instance if in cell A32 I have stored 4200 cc. I would like to reference only the 4200 for calculations in a macro in VBA. So like one can reference strings or lists in python with indices, I'd like to be able to do the same. Thank you!

Upvotes: 2

Views: 1481

Answers (3)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

Something like this(?):

Dim tmpArr
    tmpArr = Split(Range("A32"), " ")

'any calculation in vba in this way:
Range("B32") = tmpArr(0) / 100
'results with 42 in cell B32

EDIT If there is any doubt about recognition of number for first part of the split results you could make additional conversion in this way:

'conversion to Double type
Range("B32") = CDbl(tmpArr(0))/100

Upvotes: 2

baarkerlounger
baarkerlounger

Reputation: 1226

Get the cell value as a string s and return the numeric part? 

Function GetNumber(s As String) 
  Dim j As Long 
  While Not IsNumeric(Left(s, 1)) 
      If Len(s) <= 1 Then 
          Exit Function 
      Else 
          s = Mid(s, 2) 
      End If 
  Wend 
  GetNumber = Val(s) 
End Function

Upvotes: 0

quinz
quinz

Reputation: 1342

You may create a function to split the content of the cell and then handle the parts you want separately.

Dim content As Variant 
content = Split(Range("A32").Value, " ")

Upvotes: 0

Related Questions