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!

 

[Fluff]

@EXCEL MAX I think that you had better read what the OP is after again.

 

[jtakw]

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

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

Yeah, I shouldn't have used the wild card "?" in my formula which forced the SEARCH, but I would Definitely Keep the Space after the K like "K "

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,FIND("K ",A1)-3)," ",REPT(" ",LEN(A1)),1),LEN(A1),LEN(A1)))

As I said in Post # 4, if the Manufacturer has more than 1 word and/or the # of digits before K (eg. 65K) exceeds 2 digits, I can adjust the formula to accommodate.

 

[thewiseguy]

you guys are amazing. thank you all for the help!

 

[Peter_SSs]

Another UDF to possibly consider.
I don't know if it is possible to have an example like the last one below (or rows 7 or 8) but most of the other suggestions (I didn't test them all) return nothing or an error for that one.

Function Description(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b\d+W .+?(?= \d+K )"
    If .Test(s) Then Description = .Execute(s)(0)
  End With
End Function

thewiseguy.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
8	Nothing matches here
9	Max lite 28W LED Adjustable WallPack 4K 120/277	28W LED Adjustable WallPack
10	Max 4 lite 100W LED T2 Architectural AreaLight with long description 4K 120/277 Slip Fitter Mount	100W LED T2 Architectural AreaLight with long description
11	Triple-K 28W LED Adjustable WallPack 4K 120/277	28W LED Adjustable WallPack
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=Description(A2)