Excel to find key words and paste the same in Criteria column

[tinferns]

Team.. Need your help again. I believe it is possible for Excel to find key words as described by user and paste the same in the dedicated cell. And I also believe this can be done without using a Macro. Please help out on the below.. I prefer not to use Macro's if possible please.

Keyword and their expected output:

Key Words (NOT case sensitive): (This is just an example Key Word list.. actual one is approx. 20 items/values)	Result
Flight	Flight
Flights	Flight
Ticket	Air Ticket
Air Ticket	Air Ticket
Air Tickets	Air Ticket
PNR	PNR
PNR's	PNR
PNRs	PNR

Below is an example (In some cases there are 2 key words used, Excel can choose the first key word in such cases)

Description	Criteria		Expected Result
ABC bought a flight ticket paying XXX amount			Flight
XYZ bought 3 Air Tickets paying xxx amount			Air Ticket
CDE bought 1 Air Ticket vide PNR - XXXXXXX			Air Ticket

Hope to hear from you soon. Thanks.. M

 

[tinferns]

Thanks GraH for thinking that out. As a matter of fact, yes there could be situations where there could be multiple values or numbers or %age or Dates. Would we be able to track all these values ?

If so, result should look something like this:

Numbers: 3|7|3000|40
Dates: 24Jul75|03Oct73|31Mar08
%: 30%|45%|44.24%

Please advise

Thanks a million

M

 

[Peter_SSs]

Would we be able to track all these values ?

If so, result should look something like this:

I'm not sure I fully understand how you want the results. Could you post some sample data and results together so that we can see exactly what the layout should be? Preferably use XL2BB so we can easily copy for testing and see row & column labels and also copy/paste easily.

 

[tinferns]

Its far too complex working with multiple values. So lets stick to one value and one number or date or %age for now. I hope that will work. Please advise. Thanks.. M

 

[Peter_SSs]

Its far too complex working with multiple values. So lets stick to one value and one number or date or %age for now. I hope that will work. Please advise. Thanks.. M

Still ..

Could you post some sample data and results together so that we can see exactly what the layout should be? Preferably use XL2BB so we can easily copy for testing and see row & column labels and also copy/paste easily.

 

[tinferns]

Sure Peter SSs... Here you go. All I am asking for the Number, %age or date in the sentence that has a key word in it. (These keywords are just an example). Actual Key word list is bigger than the below. Thanks M

Key Word​	Actual Sentence​	Result​
Discount​	I got a 30% Discount on the new bed I bought​	30%​
Costs​	I want to buy a car which costs Rs. 300000.00​	300000​
Date​	Last date to complete this task is 25Dec20​	25-Dec-20​
Inseam​	My pant has an inseam of 32"​	32"​
Size​	I love to wear 43 size shirts​	43​

 

[Peter_SSs]

See if this does it.

tinferns.xlsm
	A	B	C
1	Key Word	Actual Sentence	Result
2	Discount	I got a 30% Discount on the new bed I bought	30%
3	Costs	I want to buy a car which costs Rs. 300000.00	300000.00
4	Costs	I love to wear 43 size shirts
5	Date	Last date to complete this task is 25Dec20	25Dec20
6	Inseam	My pant has an inseam of 32"	32"
7	Size	I love to wear big size shirts
8	Size	I love to wear 43 size shirts	43
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(ISNUMBER(SEARCH(" "&A2&" "," "&B2&" ")),IFERROR(MID(B2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},B2),1),FIND(" ",B2&" ",AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},B2),1))-AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},B2),1)+1),""),"")

 

[Peter_SSs]

This is a bit shorter and may also do the job.

tinferns.xlsm
	A	B	C
1	Key Word	Actual Sentence	Result
2	Discount	I got a 30% Discount on the new bed I bought	30%
3	Costs	I want to buy a car which costs Rs. 300000.00	300000.00
4	Costs	I love to wear 43 size shirts
5	Date	Last date to complete this task is 25Dec20	25Dec20
6	Inseam	My pant has an inseam of 32"	32"
7	Size	I love to wear big size shirts
8	Size	I love to wear 43 size shirts	43
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(ISNUMBER(SEARCH(" "&A2&" "," "&B2&" ")),IFERROR(TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",20)),AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(B2," ",REPT(" ",20))),1),20)),""),"")

 

[tinferns]

Thanks Peter SSs for both the formulas'.

Guess my above examples are different from the reality as shown below. The formula worked for 1 case. Gave wrong values for 1 case and no value for many cases,

Real time examples shown below. Can this be worked?

Key Word - Inseam
Text​	Result​
— on a date, to the market, from the gym. With elevated, denim finishing, these almost-ankle-reaching pants come in super comfy sateen fabric with an easy pull-on, elastic waistband. Perfect for ankle weather, XYZ Modern Jegging Capris will become the bottoms you can’t live without.</p> [*] <ul><li>Super stretch lasts from day to night; Snug fit doesn't lose shape </li><li>Vintage, worn-in look </li><li>Mid-rise comfort, elastic waistband </li><li>Slim through hip and thigh; slim leg opening </li><li>23" inseam </li><li>Unique fabric varies in color due to wash, finish, and dye </li><li>Machine wash</li></ul>	23"
<p>The lived-in look is here with distressed Bermuda shorts from ABC. With a high-rise stretch waist and frayed hem, edgy and comfort go hand-in-hand.</p> [*] <ul><li>Approx. Model Measurements: Height: 5'10", Waist: 26", Bust: 33", Hips: 36"</li><li>Model is wearing a size 7</li><li>Approx. inseam 10"</li><li>Zip fly; button closure</li><li>Five-pocket style</li><li>Distressed; frayed hem</li><li>Belt loops</li><li>62% Cotton/37% Polyester/1% Spandex</li><li>Machine washable</li><li>Imported</li></ul>	5'10"
<p>ABC Women's Modern Pull-On Ankle Jeggings provide the ultimate balance between comfort and fashion. Our Jeggings are created with the softest, stretchy material that accentuates every curve for a smooth, slimming silhouette. And a high-rise, pull-on waistband does double duty offering instant comfort and trend appeal. Flattering and versatile, ABC High-Rise Jeggings can be paired sneakers or heels, a long blouse or tee and fitted blazer.</p> [*] <b><br />ABC Women's Modern Pull On Ankle Jegging:<br /></b><ul><li><b>Super stretch lasts from day to night; Snug fit doesn't lose shape</b></li><li><b>Vintage, worn-in look</b></li><li><b>Pull-on, mid-rise waistband for an athletic look</b></li><li><b>Super skinny through hip and thigh; snug leg opening</b></li><li><b>29" inseam, 5-pocket styling</b></li><li><b>Unique fabric varies in color due to wash, finish, and dye</b></li><li><b>Machine wash</b></li></ul>
<p>What's not to love about XYZ Fun prints and an incredible fit that shapes and flatters make these a must-have essential.</p> [*] <ul><li>Approx. Model Measurements: Height: 5?10?, Waist: 26?, Bust: 33?, Hips: 36?</li><li>Model is wearing size S</li><li>High rise; slim fit through hips and thighs</li><li>Approx. inseam: 25"</li><li>Pull-on elastic waistband</li><li>Shaping</li><li>94% Nylon/6% Spandex</li><li>Machine washable</li><li>Imported</li></ul>
<p>Put on the <strong>ABC II Short with ABC</strong>, step outdoors, and let the adventure begin. This Hi-Tech fabric is authentic rugged adventure gear.</p> [*] <ul><li>100% quick dry nylon with stainguard</li><li>Featuring ABC</li><li>Wide belt-looped side elastic waistband</li><li>9 pockets, including front pockets with piggyback pull-tab pockets and 2 pull tab flap pockets in rear with drainage ports</li><li>Bimini Bay woven label on front pocket</li><li>7 1/2" inseam</li></ul>
<ul><li>Approx. Model Measurements: Height: 6'1", Waist: 30-1/2", Chest: 37", Shoulders: 45"</li><li>Model is wearing a size 32</li><li>Approx. inseam-10"</li><li>Belt loops</li><li>Button closure with zip fly</li><li>Front pockets; back welt pockets</li><li>Flat front</li><li>97% Cotton/3% Spandex</li><li>Machine washable</li><li>Imported</li>

 

[Peter_SSs]

Guess my above examples are different from the reality as shown below.

Yes, these examples are very different and I was misled by

lets stick to .... one number or date or %age for now.

Also, I am still not sure we have representative data as all these latest examples are with "Inseam" and all values are in inches ("). If this is not the case can we have a more realistic (varied) set of sample data and expected results?

 

[tinferns]

Hi Peter SSs.. thanks for your reply. Apologies again for misleading you. Below are 2 options of keywords:

Inseam - Will mostly be in inches. However its fine if you give me only the number. " Sign is optional. I also see that it will be difficult for you to give me numbers before / and / or / after the key word. So lets stick to after key word.

Size - This could be numbers or alphabets. Eg. Size S or Size: XL or Size- 32 X 32.

Is the above do'able ?

Please advise..

Thanks,

M