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]

Maybe a UDF ?

Function PON(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "([0.9 0-9]+?[W|kW]+)"
  If .Test(s) Then PON = .Execute(s)(0).SubMatches(0)
End With
End Function

Excel 2007
	A	B
1	aaksdhasdkjh 400W asjhasdjh	400W
2	blabla 400W blablabla blabla	400W
3	blabla,0.5kW blabla bla bla bla	0.5kW
4	blabla,25.5kW blabla bla bla bla	25.5kW
Sheet1

Cell Formulas
Range	Formula
B1	=PON(A1)
B2	=PON(A2)
B3	=PON(A3)
B4	=PON(A4)

 

[phoenixdown]

Be a little more helpful if you had real data to look through. Someone elses excel ninjitsu may be able to do something with what you have my mine is not so much lol

 

[dudumomo]

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")

Thanks a lot!

 

[Michael M]

you'll need to provide a couple more examples of the data AND what you want as a result....Excel doesn't really know which you want, so it provides the first sample.
If there is always a "-" between the numbers ....try using text to columns to split them first, with "-" as the delimiter !

 

[dudumomo]

Sure, let me give you real data with the expected results

1510001464#&Bộ lưu điện dự phòng 500KVA 3P/4W 380V	500KVA
1510001465#&Bộ lưu điện dự phòng 500kva 3P/4W 380V	500kva
KDTC-3307#&Nguồn S8VS-06024 (chuyển đổi nguồn điện đầu vào AC 220V xoay chiều đầu ra 24V một chiều), hàng mới 100%	#N/A
Bộ lưu điện ABC Offline 1000VA - Model CV1000 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	1000VA
Bộ lưu điện Lumix Online 500vaa - Model LU500 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	500va
Bộ lưu điện 8V6B-500VA - Model 8V6B500 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	500VA

<colgroup><col><col></colgroup><tbody>
</tbody>

PS: Sometimes it's in UPPER or lower case.

Thanks a lot!

 

[Michael M]

sorry, I'm confused....what does counting UPPER and LOWER case have to do with extracting the results.
I think you need to post a sample of data with what you want to do with it.
Adding to the requirements with each post makes it very difficult to provide useful information.
Have a think about everything you need, then post back !

 

[István Hirsch]

Sure, let me give you real data with the expected results

1510001464#&Bộ lưu điện dự phòng 500KVA 3P/4W 380V	500KVA
1510001465#&Bộ lưu điện dự phòng 500kva 3P/4W 380V	500kva
KDTC-3307#&Nguồn S8VS-06024 (chuyển đổi nguồn điện đầu vào AC 220V xoay chiều đầu ra 24V một chiều), hàng mới 100%	#N/A
Bộ lưu điện ABC Offline 1000VA - Model CV1000 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	1000VA
Bộ lưu điện Lumix Online 500vaa - Model LU500 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	500va
Bộ lưu điện 8V6B-500VA - Model 8V6B500 (Kèm sách hướng dẫn sử dụng), (Hàng mới 100%)	500VA

<tbody>
</tbody>

PS: Sometimes it's in UPPER or lower case.

Thanks a lot!

In the 5th line 500vaa is a typo?

 

[dudumomo]

Hi Michael,
I've edited my message while you were writting yours. Hope my revised message makes more sense.

Thank you

 

[dudumomo]

Hi István,
It is a typo from the source. (Not from me). So we still need to show only 500va xD