CODEWITHSUNDEEP
excelexcel-formula
YorkieMagento
YorkieMagento

Reputation: 366

Find word plus additional characters in Excel cell

I have an excel sheet with text in a cell - I'd like to extract the specific text and an additional 5 characters alongside it.

For example:

Cell A1 contains the text - 'Here is the cell for widget 1A1A and its content'. I'd like to find any cell that contains the word 'widget' and extract that and the following 5 characters (including space). So the result would be 'Widget 1A1A'.

Upvotes: 0

Views: 384

Answers (1)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

This is quite straightforward. Use the SEARCH command to find the word "widget" (FIND is the same but case-sensitive):

=SEARCH("widget",A1)

This gives you the character number in the cell where the match is found [if no match is found it returns "#N/A".

Then match this with the MID function to pull in the text "Widget" + the following 5 characters, as follows:

=MID(A1,SEARCH("widget",A1), LEN("widget") + 5)

Upvotes: 2

Related Questions