Search criteria in a single cell

[Gani]

Please help me in this regard. i'm using one store excel but it contains all data in single cell they are maintaining the data since long back now i need to take out some data for further analysis.

Ex: in one cell data is like this

WO/ 56180 / 28 750 m resved by pragathi pachipala on 12/8/2012 WO/ 56180-27 950m resved by Rajeswara Rao on 12/8/2012 W/O 56193/22 1250 m resved byNarim Ganesh on 13/8/2012 | WO 55899-88 2000m by Asif on 14/8/2012 | Reserved for WO 56180/16 (950m) by Pragathi on 14/8/2012

Now i want to calculate total lenght from this cell by counting 750m+950m+1250m+2000m+950m = ??
it is very helpful tip for me if one can tell the answer.

 

[Gani]

If I understand you correctly, you want to add all the numbers in the string that are followed by the letter "m", and there may or may not be <space> between the number and the "m". In addition, all of the numbers are integers.



That being the case, this is best (easiest) done with a User Defined Function:



To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.

Ensure your project is highlighted in the Project Explorer window.

Then, from the top menu, select Insert/Module and

paste the code below into the window that opens.



To use this User Defined Function (UDF), with your string in A1, enter a formula like



=SumData(A1)



in some cell.







==================================

Option Explicit

Function SumData(s As String) As Variant

Dim re As Object, mc As Object

Dim v As Variant

Dim i As Long

Set re = CreateObject("vbscript.regexp")

With re

.Global = True

.Pattern = "\d+(?=\s*m)"

If .test(s) = True Then

Set mc = .Execute(s)

ReDim v(1 To mc.Count)

For i = 1 To mc.Count

v(i) = mc(i - 1)

Next i

Else

SumData = CVErr(xlErrNA)

Exit Function

End If

End With

SumData = Evaluate(Join(v, "+"))

End Function

=============================

This formula shows error like " This formula contains unrecognized text"

 

[Gani]

Tough but one way with the example given



Option Explicit

Sub SumMvaluesInStringSAS()

Dim ms As Long

Dim r As Range

Dim h As Long

Dim i As Long

Dim x As Long



Set r = Range("a9")

'MsgBox r

r = Replace(r, ")", "")

r = Replace(r, "(", " ")

r = Replace(r, " m", "m")

MsgBox r ' ms

h = 1

On Error GoTo nomo

For i = 1 To Len(r)

x = InStr(h, r, "m")

'MsgBox X

If IsNumeric(Mid(r, x - 1, 1)) Then

'MsgBox Mid(r, X - 4, 4)

ms = ms + Mid(r, x - 4, 4)

End If

h = x + 1

Next i

nomo:

MsgBox Format(ms, "#,###")

End Sub

Could you please explain me how to this code in excel since i'm not aware of VBA code.

 

[Don Guillett]

Please help me in this regard. i'm using one store excel but it contains all data in single cell they are maintaining the data since long back now ineed to take out some data for further analysis.



Ex: in one cell data is like this



WO/ 56180 / 28 750 m resved by pragathi pachipala on 12/8/2012 WO/ 56180-27 950m resved by Rajeswara Rao on 12/8/2012 W/O 56193/22 1250 m resved by Narim Ganesh on 13/8/2012 | WO 55899-88 2000m by Asif on 14/8/2012 | Reserved for WO 56180/16 (950m) by Pragathi on 14/8/2012



Now i want to calculate total lenght from this cell by counting 750m+950m+1250m+2000m+950m = ??

it is very helpful tip for me if one can tell the answer.

Send your file to dguillett1 @gmail.com with this msg