Extract 1 string between space (or ,) and W (or kW)

[dudumomo]

Hi there,

I have some lines like these:
blablabla blabla bla 400W blabla ==> 400W
blabla 400W blablabla blabla ==> 400W
blabla,0.5kW blabla bla bla bla ==> 0.5kW

I'm trying to build a formula to extract those data accordingly, but seems they can be betwen a coma or a space and a kW or W, I have difficulties to extract the data.
And also, we have to make sure before the kW or W, is a number. As some brand name or description could be let's say "Cowboy", we should not take this one but only when there is a number.

Any idea on how to do it?
The best will also be if kW, it converts in W (/1000) to get clean data.

Thanks for your help!

 

[Michael M]

sorry, best I can do is....can't remove the Vaa or come up with the N/A.....I'm sure there a far better ways !!

Function VON(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "([0-9]{2,}[VA|KVA|va|kva]+)"
  If .Test(s) Then VON = .Execute(s)(0).SubMatches(0)
End With
End Function

Excel 2007
	A	B
1	1510001464#&B? luu di?n d? phng 500KVA 3P/4W 380V	500KVA
2	1510001465#&B? luu di?n d? phng 500kva 3P/4W 380V	500kva
3	KDTC-3307#&Ngu?n S8VS-06024 (chuy?n d?i ngu?n di?n d?u vo AC 220V xoay chi?u d?u ra 24V m?t chi?u), hng m?i 100%	220V
4	B? luu di?n ABC Offline 1000VA - Model CV1000 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	1000VA
5	B? luu di?n Lumix Online 500vaa - Model LU500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500vaa
6	B? luu di?n 8V6B-500VA - Model 8V6B500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500VA
Sheet1

Cell Formulas
Range	Formula
B1	=von(A1)
B2	=von(A2)
B3	=von(A3)
B4	=von(A4)
B5	=von(A5)
B6	=von(A6)

 

[István Hirsch]

@ Michael:

In row 3 your formula gives 220v instead of N/A

Edit: I see now that you know that

 

[dudumomo]

It is already a nice improvement. Thanks!

It's not perfect for the 220v and 500vaa tho, but I did not find a good parameter neither. It already helps to clean the data.

Not sure if there is a better way tho?

Anyone if another idea?

Thank you!!

 

[Peter_SSs]

Is this any better?

Function ExtractVA(s As String) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "\d*\.?\d* *k?va"
    ExtractVA = .Execute(s)(0)
  End With
End Function

Excel Workbook
	A	B
1
2	1510001464#&B? luu di?n d? phng 500KVA 3P/4W 380V	500KVA
3	1510001465#&B? luu di?n d? phng 500kva 3P/4W 380V	500kva
4	KDTC-3307#&Ngu?n S8VS-06024 (chuy?n d?i ngu?n di?n d?u vo AC 220V xoay chi?u d?u ra 24V m?t chi?u), hng m?i 100%	#VALUE!
5	B? luu di?n ABC Offline 1000VA - Model CV1000 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	1000VA
6	B? luu di?n Lumix Online 500vaa - Model LU500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500va
7	B? luu di?n 8V6B-500VA - Model 8V6B500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500VA
8	bla bla 33.24 VA etc	33.24 VA
9
VA KVA

 

[István Hirsch]

@ Peter

For me it does not extract 500KVA in:

1510001464#&Bộ lưu điện model va phòng 500KVA 3P/4W 380VA

<tbody>
</tbody>

 

[Peter_SSs]

@ Peter

For me it does not extract 500KVA in:

1510001464#&Bộ lưu điện model va phòng 500KVA 3P/4W 380VA

<tbody>
</tbody>

You are correct.

That resulted from me trying to allow for decimal numbers before the VA or KVA, even though were no such example in the sample data for VA/KVA. I can correct that but before doing so it would be beneficial to know whether ..

a) Decimal values could occur?

b) Two or more VA/KVA values are possible in the string like in István's sample ("500KVA" and 380VA") and, if so, what result(s) should be returned?

 

[István Hirsch]

You are correct.

That resulted from me trying to allow for decimal numbers before the VA or KVA, even though were no such example in the sample data for VA/KVA. I can correct that but before doing so it would be beneficial to know whether ..

a) Decimal values could occur?

b) Two or more VA/KVA values are possible in the string like in István's sample ("500KVA" and 380VA") and, if so, what result(s) should be returned?

380VA remained accidentally in the sample that I used to test my version under construction how it works in end position – it does not (but yours works). So the end string to be considered is the original 380V.

 

[Michael M]

Peter
Really like the function, well done ( as always).......I don't think I will ever completely come to grips with Regex.
Is there a GOOD learning resource available ?
They all seem so complex...ray:

 

[Rick Rothstein]

Thanks Michael for your reply!
Nice approach and it works.

I have another similar, but with VA and KVA. The issue here is sometimes, I got product name with 8V6B-500VA and the formula give me 8V. How can I force "VA" (Not only 'V")

Does this UDF (user defined function) work for you...

Function ExtractVA(S As String) As String
  Dim X As Long, Txt As String, VA() As String
  VA = Split(Replace(S, "-", " "), "va", , vbTextCompare)
  For X = 0 To UBound(VA) - 1
    Txt = Mid(VA(X), InStrRev(VA(X), " ") + 1)
    If Len(Txt) > 0 And Application.IsNumber(Val(Txt)) Then
      ExtractVA = UCase(Txt) & "VA"
      Exit For
    End If
  Next
End Function

 

[Peter_SSs]

Does this UDF (user defined function) work for you...

Function ExtractVA(S As String) As String
  Dim X As Long, Txt As String, VA() As String
  VA = Split(Replace(S, "-", " "), "va", , vbTextCompare)
  For X = 0 To UBound(VA) - 1
    Txt = Mid(VA(X), InStrRev(VA(X), " ") + 1)
    [COLOR="#0000CD"]If Len(Txt) > 0 And Application.IsNumber(Val(Txt)) Then[/COLOR]
      ExtractVA = UCase(Txt) & "VA"
      Exit For
    End If
  Next
End Function

"Give an ovation for the 500KVA item"

Just wondering about the blue line?

If Len(Txt) And Val(Txt) Then