How to extract the middle of a string?

[thewiseguy]

Hi All,

I have a long list of products which contain multiple pieces of information in the string. I need to "clean up" the string of text and so I only need some of the information to be displayed.

Here is an example of the data that needs to be cleaned: Maxlite 100W LED T2 Architectural AreaLight 4K 120/277 Slip Fitter Mount

Here is what I would like the output to be: 100W LED T2 Architectural AreaLight

Each line of data is some variation of this. The first word is ALWAYS the manufacturer and can be removed.
Each line of data always has some variation of "4K." It could be 35K, 4K, 65K, but it will always end in "K."

Is there a formula I can use to clean up a few hundred lines of data as shown above?

Many thanks in advance!

 

[Trebor76]

Hi thewiseguy,

Using the nifty formula by barry houdini here try this (just change the initial cell reference from A2 to where your data starts if need be):

=MID(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",4))-1),FIND("^^",SUBSTITUTE(A2," ","^^",1))+1,LEN(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",4))-1)))

Regards,

Robert

 

[Trebor76]

Actually try this:

=MID(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1),FIND("^^",SUBSTITUTE(A2," ","^^",1))+1,LEN(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1)))

 

[jtakw]

Hi,

Assuming the "manufacturer" is Always just a Single word at the beginning (if not, I'll adjust the formula to suit).
Also, that the "K" will not exceed 2 digits (e.g. not 100K)

Try this:

Book3.xlsx
	A	B
1	Maxlite 100W LED T2 Architectural AreaLight 4K 120/277 Slip Fitter Mount	100W LED T2 Architectural AreaLight
2	Maxlite 40W LED T9 Architectural AreaLight 65K 120/277 Slip Fitter Mount	40W LED T9 Architectural AreaLight
Sheet908

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(MID(SUBSTITUTE(LEFT(A1,SEARCH("?K ",A1)-2)," ",REPT(" ",LEN(A1)),1),LEN(A1),LEN(A1)))

 

[thewiseguy]

Actually try this:

=MID(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1),FIND("^^",SUBSTITUTE(A2," ","^^",1))+1,LEN(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1)))

So this worked perfectly, except for a few values:

Original Value:

Actually try this:

=MID(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1),FIND("^^",SUBSTITUTE(A2," ","^^",1))+1,LEN(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",6))-1)))

So this worked perfectly with the exception of a few values:

ORIGINAL VALUE: Maxlite 28W LED Adjustable WallPack 4K 120/277
OUTPUT: 28W LED Adjustable WallPack 4K
ERROR: The "4K" should not be visible

ORIGINAL VALUE: Maxlite 200W LED Xlarge Flood 4K 120/277 Slip Fitter Mount
OUTPUT: 200W LED Xlarge Flood 4K
ERROR: The "4K" should not be visible

I wish I could decipher the formula you provided but it is a bit above my pay grade. Any ideas?

Thank you again.

 

[mohadin]

Hi
What about VBA

Sub test()
    Dim a As Variant
    Dim i As Long
    a = Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(^\w+) |(\d+[kK].+)"
        For i = 1 To UBound(a)
     a(i, 1) = .Replace(a(i, 1), "")
        Next
    End With
    Cells(1, 2).Resize(UBound(a)) = a
End Sub

 

[Trebor76]

Any ideas?

I would actually use this slight variation of jtakw's nifty suggestion:

=TRIM(MID(SUBSTITUTE(LEFT(A2,FIND("K",A2)-3)," ",REPT(" ",LEN(A2)),1),LEN(A2),LEN(A2)))

If the output for all cases is still not correct then mohadin's VBA solution might be the way to go as there can be so many variations with data formulas can become cumbersome and complex trying to get consistent results.

HTH

Robert

 

[Fluff]

Another option if you have the LET function

+Fluff 1.xlsm
	A	B
1
2	Maxlite 100W LED T2 Architectural AreaLight 4K 120/277 Slip Fitter Mount	100W LED T2 Architectural AreaLight
3	Maxlite 40W LED T9 Architectural AreaLight 65K 120/277 Slip Fitter Mount	40W LED T9 Architectural AreaLight
4	Maxlite 28W LED Adjustable WallPack 4K 120/277	28W LED Adjustable WallPack
5	Maxlite 2000W LED Xlarge Flood 4K 120/277 Slip Fitter Mount	2000W LED Xlarge Flood
6	Maxlite 9W LED Xlarge Flood 400K 120/277 Slip Fitter Mount	9W LED Xlarge Flood
7	Max lite 28W LED Adjustable WallPack 4K 120/277	28W LED Adjustable WallPack
Test

Cell Formulas
Range	Formula
B2:B7	B2	=LET(Txt,SUBSTITUTE(A2," ",REPT(" ",100)),TRIM(MID(LEFT(Txt,FIND("K ",Txt)-20),FIND("W ",Txt)-20,LEN(Txt))))

 

[kvsrinivasamurthy]

ThIs UDF code works

Function ExtractData(Ip As String)
Dim M, K


K = False
M = Split(Ip, " ")


For T = 0 To UBound(M)
If InStr(1, M(T), "W") > 0 Then
K = True
ElseIf InStr(1, M(T), "K") > 0 Then
Exit For
End If


If K = True Then Temp = Temp & " " & M(T)
Next T


If Temp <> "" Then ExtractData = Mid(Temp, 2) Else ExtractData = ""
End Function

UDF

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List

Say A2 is having data.
In B2 formula

=ExtractData(A2)

 

[EXCEL MAX]

One more UDF...

Function ExtractPart(ByVal vR As Range)
  
    Dim vA, vA2()

    vA = Split(vR, " ")
    ReDim vA2(5)
    For x = 1 To 5
        vA2(x) = vA(x)
    Next x
    ExtractPart = Trim(Join(vA2, " "))

End Function