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!

 

[Rick Rothstein]

Just wondering about the blue line?

If Len(Txt) And Val(Txt) Then

Actually, now that you mention it, and if I am not mistaken, I think your suggestion can be simplified even further to this...

If Val(Txt) Then

 

[Rick Rothstein]

Actually, now that you mention it, and if I am not mistaken, I think your suggestion can be simplified even further to this...

If Val(Txt) Then

Nope... my original code, your simplification to it and my simplification to your simplification all fail if there is text after the number, whether that text is the letter "k" or not, followed by "va" (for example, 123peterva). I believe this modification properly handles this problem...

[table="width: 500"]
[tr]
	[td]Function ExtractVA(S As String) As String
  Dim X As Long, Txt As String, TxtNum 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)
    TxtNum = Txt
    If Txt Like "*[Kk]" Then TxtNum = Left(Txt, Len(Txt) - 1)
    If Val(TxtNum) And (Not TxtNum Like "*[!0-9.]*" And Not TxtNum Like "*.*.*" And TxtNum <> ".") Then
      ExtractVA = UCase(Txt) & "VA"
      Exit For
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

I think I'll wait and hope the OP answers the questions asked in post #16 as well as ..

3. Is is possible that a space exists between the number and the (k)VA?
eg Is this possible?
1510001464#&B? luu di?n d? phòng 500 KVA 3P/4W 380V

 

[Rick Rothstein]

3. Is is possible that a space exists between the number and the (k)VA?
eg Is this possible?
1510001464#&B? luu di?n d? phòng 500 KVA 3P/4W 380V

If so, and if the OP does not mind losing that space in the output, then the following macro will retrieve any number before, va or kva (any case) whether the va or kva is preceded by a space or not...

[table="width: 500"]
[tr]
	[td]Function ExtractVA(S As String) As String
  Dim X As Long, Txt As String, VA() As String, Parts() As String
  VA = Split(Replace(S, "-", " "), "va", , vbTextCompare)
  For X = 0 To UBound(VA) - 1
    Parts = Split(Trim(Replace(VA(X), "k", " ", , , vbTextCompare)))
    Txt = Parts(UBound(Parts) + (Right(Parts(UBound(Parts)), 1) Like "[Kk]"))
    If Val(Txt) And (Not Txt Like "*[!0-9.]*" And Not Txt Like "*.*.*" And Txt <> ".") Then
      ExtractVA = Txt & UCase(Right(VA(X), -(Right(VA(X), 1) Like "[Kk]"))) & "VA"
      Exit For
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[dudumomo]

Wow that's great and very powerful.
Well the quality of data is terrible and yes, in some cases they may add a space before VA or KVA.

But I also found some results that does not work with this formula:
Bộ nguồn cấp điện liên tục hiệu PROTP: PRA852S (3KVA), hàng mới 100%, Bộ=Cái

Bộ cấp nguồn liên tục (380V ,3P,200KVA), hàng mới 100% Bộ lưu điện 3 pha Công suất100KVA, hãng sx KKKST Thanks!

<tbody>
</tbody>

 

[Rick Rothstein]

But I also found some results that does not work with this formula:
Bộ nguồn cấp điện liên tục hiệu PROTP: PRA852S (3KVA), hàng mới 100%, Bộ=Cái

Bộ cấp nguồn liên tục (380V ,3P,200KVA), hàng mới 100% Bộ lưu điện 3 pha Công suất100KVA, hãng sx KKKST

<tbody>
</tbody>

See if this works better for you...

[table="width: 500"]
[tr]
	[td]Function ExtractVA(S As String) As String
  Dim X As Long, Z As Long, Txt As String, VA() As String, Parts() As String
  VA = Split(Replace(S, "-", " "), "va", , vbTextCompare)
  For X = 0 To UBound(VA) - 1
    For Z = 1 To Len(VA(X))
      If Mid(VA(X), Z, 1) Like "[!0-9KVAkva]" Then Mid(VA(X), Z) = " "
    Next
    Parts = Split(Application.Trim(Replace(VA(X), "k", " ", , , vbTextCompare)))
    Txt = Parts(UBound(Parts) + (Right(Parts(UBound(Parts)), 1) Like "[Kk]"))
    If Val(Txt) And (Not Txt Like "*[!0-9.]*" And Not Txt Like "*.*.*" And Txt <> ".") Then
      ExtractVA = Txt & UCase(Right(VA(X), -(Right(VA(X), 1) Like "[Kk]"))) & "VA"
      Exit For
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[dudumomo]

Nice! Works well
Big thanks to all of you.

EDIT: Well I found another issue, I have a line like this:
model: A-500M-5 . AC: 200-240VAC and the formula give me 240VA. But in fact it's VAC, not VA...can we improve this?
Thanks

EDIT2:
Now I'm trying to do something similar, to extract
Model: ABC, blabla
To extract the ABC, but it can be followed by . , : ; or can start by "Model:" or "Model" or "Model :"
It can have a space after "Model:" or not...

Wow I need to learn this!

 

[Peter_SSs]

Well the quality of data is terrible and yes, in some cases they may add a space before VA or KVA.

What about my earlier questions?

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

EDIT: Well I found another issue, I have a line like this:
model: A-500M-5 . AC: 200-240VAC and the formula give me 240VA. But in fact it's VAC, not VA...can we improve this?

You have told us what is returned and indicated that it isn't what you want, but you haven't confirmed what result you do want from such an entry.

 

[dudumomo]

Hi Peter,

Regarding your questions:
a) Decimal values can occur like 1.5kVA instead of 1500VA
b) This should not happen, but if it does, I don't have any rules...except if twice the same value. But if different values, I will need to manually check (May be we can display a different error type?)

And for the VAC example, it should provide BLANK results like it did not found anything.

Thank you

 

[Peter_SSs]

To me, part of your request seems impossible to understand the logic

Bộ lưu điện Lumix Online 500vaa - Model
From this you said you wanted to return 500va because the "vaa' was a mistake from the source and you wanted 500va returned

model: A-500M-5 . AC: 200-240VAC
From this you say you want blank returned.

How would we logically decide if the letter following the "va" was to be ignored as in the first example or not ignored as in the second example?