SOLVED
Home

finding a specified word from the sentence in the cell and put in the other cell

%3CLINGO-SUB%20id%3D%22lingo-sub-293745%22%20slang%3D%22en-US%22%3Efinding%20a%20specified%20word%20from%20the%20sentence%20in%20the%20cell%20and%20put%20in%20the%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293745%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20very%20long%20door%20specification%20table.%20In%20the%20attached%20file%20in%20the%20%22A%22%20column%20the%20specification%20of%20door%20is%20written%20as%20a%20sample.%20I%20want%20to%20see%20in%20the%20%22B%22%20column%20the%20material%20of%20door%20.%20I%20wrote%20by%20hand%20in%20the%20%22B%22%20column%20each%20of%20the%20doors%20material%20%2C%20but%20I%20want%20that%20excel%20take%20specified%20word%20from%20in%20the%20%22A%22%20column%20and%20writes%20by%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-293745%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293755%22%20slang%3D%22en-US%22%3ERe%3A%20finding%20a%20specified%20word%20from%20the%20sentence%20in%20the%20cell%20and%20put%20in%20the%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293755%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20it%20works%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMID(A2%2C%2012%2C%206)%20returns%20text%20which%20is%20in%20A2%20starting%20from%2012th%20positions%20and%206%20characters%20length%3B%3C%2FP%3E%0A%3CP%3ESEARCH(%22%20cm%20%22%2C%20A2)%20finds%20the%20position%20of%20the%26nbsp%3B%3CSPAN%3E%22%20cm%20%22%20in%20A2.%20%2B4%20gives%20where%20next%20word%20starts%20(since%26nbsp%3B%22%20cm%20%22%20is%204%20characters%20length).%20Let%20say%20it'll%20be%2012.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESEARCH(%22door%22%2C%20A2)%20finds%20the%20position%20where%26nbsp%3B%22door%22%20starts.%20Extracting%20from%20it%20previous%20result%20minus%20one%20we%20receive%20the%20length%20of%20the%20text%20to%20extract%20(let%20say%206).%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293750%22%20slang%3D%22en-US%22%3ERe%3A%20finding%20a%20specified%20word%20from%20the%20sentence%20in%20the%20cell%20and%20put%20in%20the%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293750%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20yes%20it%20works%201%20exactly%20what%20I%20wanted.%20Thank%20you%20so%20much.%3C%2FP%3E%3CP%3EHonestly%20I%20may%20say%20that%20I%20didn't%20understand%20how%20it%20works%20this%20magical%20formula%20but%20really%20great%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293749%22%20slang%3D%22en-US%22%3ERe%3A%20finding%20a%20specified%20word%20from%20the%20sentence%20in%20the%20cell%20and%20put%20in%20the%20other%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293749%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Mustafa%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DMID(A2%2CSEARCH(%22%20cm%20%22%2CA2)%2B4%2CSEARCH(%22door%22%2CA2)-SEARCH(%22%20cm%20%22%2CA2)-5)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
mustafa kun
New Contributor

Hello,

I have very long door specification table. In the attached file in the "A" column the specification of door is written as a sample. I want to see in the "B" column the material of door . I wrote by hand in the "B" column each of the doors material , but I want that excel take specified word from in the "A" column and writes by itself.

 

3 Replies
Highlighted
Solution

Hi Mustafa,

 

That could be

=MID(A2,SEARCH(" cm ",A2)+4,SEARCH("door",A2)-SEARCH(" cm ",A2)-5)

Hello Sergei,

 

Thank you, yes it works 1 exactly what I wanted. Thank you so much.

Honestly I may say that I didn't understand how it works this magical formula but really great :)

How it works:

 

MID(A2, 12, 6) returns text which is in A2 starting from 12th positions and 6 characters length;

SEARCH(" cm ", A2) finds the position of the " cm " in A2. +4 gives where next word starts (since " cm " is 4 characters length). Let say it'll be 12.

SEARCH("door", A2) finds the position where "door" starts. Extracting from it previous result minus one we receive the length of the text to extract (let say 6).

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies