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!

 

[dudumomo]

...right... well I will still need some manual checks then.
Thanks for clarifying it.

 

[Peter_SSs]

...right... well I will still need some manual checks then.
Thanks for clarifying it.

OK, so here's my next attempt. You may need to do some manual checking as discussed.

Function ExtractVA(s As String) As String
  Dim x As Object
  Dim i As Long
  
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Global = True
    .Pattern = "(\d+,?)+\.?\d* *k?va"
    Set x = .Execute(s)
    For i = 1 To x.Count
      ExtractVA = ExtractVA & ", " & x(i - 1)
    Next i
    ExtractVA = Mid(ExtractVA, 3)
  End With
End Function

Excel Workbook
	A	B
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%
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 ABC Offline 1,254.63VA - Model CV1000 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	1,254.63VA
7	B? luu di?n Lumix Online 500vaa - Model LU500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500va
8
9	B? luu di?n 8V6B-500VA - Model 8V6B500 (Km sch hu?ng d?n s? d?ng), (Hng m?i 100%)	500VA
10	bla bla 33.24 VA etc	33.24 VA
11	bla bla 0.24 kVA etc	0.24 kVA
12	1510001464#&B? luu di?n model va phng 500KVA 3P/4W 380VA	500KVA, 380VA
13	1510001464#&B? luu di?n model va phng
14	model: A-500M-5 . AC: 200-240VAC and the formula give me 250KVA.	240VA, 250KVA
15
VA KVA

 

[dudumomo]

Nice!

And if we ignore the 500vaa and to stick to 500va? Because I have a lot with VAC and very few with error like vaa. WIll be easier for me to clean.

Thanks a lot!

 

[Peter_SSs]

Nice!

And if we ignore the 500vaa and to stick to 500va? Because I have a lot with VAC and very few with error like vaa. WIll be easier for me to clean.

Thanks a lot!

To ignore items like "vaa" or "VAC" then try just changing this line

.Pattern = "(\d+,?)+\.?\d* *k?va(?= |$)"

 

[István Hirsch]

@ Peter

As far as I can see with the new Pattern the code does not recognize the search string followed by punctuation mark, for example "500 KVA,".

 

[Peter_SSs]

@ Peter

As far as I can see with the new Pattern the code does not recognize the search string followed by punctuation mark, for example "500 KVA,".

Thanks for pointing that out István.

That was (mistakenly) deliberate as I thought all the OP's valid data had a space after the "VA". On receiving your message and re-checking the thread, I now remember post #25.

I would see if this changed pattern line suited the OPs requirements

.Pattern = "(\d+,?)+\.?\d* *k?va(?![a-z])"

 

[dudumomo]

Thank you, it works quite well.

 

[Peter_SSs]

Thank you, it works quite well.

Cheers, thanks for letting us know.