SOLVED

Cell to column and returning values

%3CLINGO-SUB%20id%3D%22lingo-sub-2588570%22%20slang%3D%22en-US%22%3ECell%20to%20column%20and%20returning%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2588570%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20have%20a%20formula%20that%20search%20a%20cell%20for%20a%20specific%20word%20and%20then%20returns%20the%20next%2010-20%20characters%20after%20the%20word%20including%20the%20word%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx.%20Bananas%3A%20Yellow.%20Apples%3A%20Green%20and%20Red.%20Lettuce%3A%20Green.%20Carrots%3A%20Orange.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20was%20in%20a%20cell%20can%20you%20search%20for%20Lettuce%20and%20return--%26nbsp%3B%20Lettuce%3A%20Green%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2588570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2588613%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20to%20column%20and%20returning%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2588613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1113121%22%20target%3D%22_blank%22%3E%40Shaheen895%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20text%20%22Bananas%3A%20Yellow.%20Apples%3A%20Green%20and%20Red.%20Lettuce%3A%20Green.%20Carrots%3A%20Orange.%22%20is%20in%20A1.%3C%2FP%3E%0A%3CP%3EIn%20B1%2C%20you%20enter%20Bananas%2C%20or%20Lettuce%2C%20or%20...%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C1%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(MID(A1%2CFIND(B1%2CA1)%2CFIND(%22.%22%2CA1%2CFIND(B1%2CA1))-FIND(B1%2CA1))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Does anyone have a formula that search a cell for a specific word and then returns the next 10-20 characters after the word including the word?

 

Ex. Bananas: Yellow. Apples: Green and Red. Lettuce: Green. Carrots: Orange. 

 

If that was in a cell can you search for Lettuce and return--  Lettuce: Green?

2 Replies
best response confirmed by Shaheen895 (New Contributor)
Solution

@Shaheen895 

Let's say the text "Bananas: Yellow. Apples: Green and Red. Lettuce: Green. Carrots: Orange." is in A1.

In B1, you enter Bananas, or Lettuce, or ...

Enter the following formula in C1:

=IFERROR(MID(A1,FIND(B1,A1),FIND(".",A1,FIND(B1,A1))-FIND(B1,A1)),"")
Awesome! Thanks!