extract number with decimal from a sentence or words

[jeevankg09@gmail.com]

Hi everyone..

I have a problem for which I seek your help.

I have daily reporting format given by construction and these contains comibination of qty and activity. One such e.g is below.


Concrete pouring (2.84m3) in say cell A1 and..

concrete pouring for 1 part (3.4m3)

concrete pouring for 2 part (4.1m3) together in cell A2.I want to extract A1 content to B1 2.84 and similarly to B2 (3.4+4.1)=7.5..Does excel can work this out or should use VBA function..?

 

[Marc L]

Hi !   For A1, by a formula it's possible (easy).

        I'm not sure for A2 but still possible with a VBA function (not difficult) …


I prefer to wait if someone have an idea for A2 by formula before to create the VBA code …

 

[amrit1186]

Hi jeevankg09


concrete pouring for 1 part (3.4m3)

concrete pouring for 2 part (4.1m3)


Can there be only 2 lines in a cell or more?


Amritansh

 

[oldchippy]

Hi jeevankg09@gmail.com,


Welcome to the forum


For the first one,


=MID(A1,FIND("(",A1)+1,FIND(")",A1)-3-FIND("(",A1))+0


For the second one,


=MID(A1,FIND("(",A1)+1,FIND(")",A1)-3-FIND("(",A1))+MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,FIND(")",A1,FIND(")",A1)+1)-3-FIND("(",A1,FIND("(",A1)+1))+0


Rather long but I'm sure someone could shorten

 

[Debraj]

Hi Jeevankg!


If VBA acceptable.. then I would like to use below function..

[pre]

Option Compare Text
Function jeevankg(sentence As Range) As Double
deb = Split(Replace$(sentence, vbCrLf, " "), " ")
For i = 0 To UBound(deb)
If deb(i) Like "*([0-9.]*m3)*" Then
deb(i) = Replace(deb(i), "m3", "")
raj = ""
For X = 1 To Len(deb(i))
If Mid(deb(i), X, 1) Like "[0-9.]" Then
raj = raj & Mid(deb(i), X, 1)
End If
Next X
jeevankg = jeevankg + CDbl(raj)
End If
Next i
End Function

[/pre]

In B1 use Formula as =jeevankg(A1)


Regards,

Deb

 

[Marc L]

Another VBA way :

[pre]

Function ExtractQty(Rg As Range)
P = InStr(Rg(1), "(")

Do While P
Q = Q + Val(Mid(Rg(1), P + 1))
P = InStr(P + 1, Rg(1), "(")
Loop

ExtractQty = Q
End Function

[/pre]