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.

 

[Ron Rosenfeld]

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 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.

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
=============================

 

[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.

 

[Ron Rosenfeld]

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

I had pasted instructions in my response, which you did not copy here.
What didn't you understand?
Please be very specific as to what you are not understanding in these instructions, because I do not know how to make them more clear otherwise.

Here they are again.

======================
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.
========================

 

[Ron Rosenfeld]

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

Exactly where are you seeing that error?
Please copy and paste the code, exactly how you entered it.
Please be sure you entered the code exactly according to my instructions as to how to do it.

 

[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