Solved finding text in a cell and returning that text

July 20, 2016 at 10:06:24
Specs: Windows 7
How do I search a cell for a specific word and then output that word in another cell. Or I can output another cells contents after a part of text. For example the "-" below

Ex A1 contains "Complete Draft - Batch", I want the output to be "Batch"
I have many scenarios so I need to nest these statements


See More: finding text in a cell and returning that text

Report •

#1
July 20, 2016 at 10:25:02
✔ Best Answer
You may have asked 2 different questions, but I'm not sure.

1 - How do I search a cell for a specific word and then output that word in another cell.

2 - Or I can output another cells contents after a part of text. For example the "-" below

Searching for a specific word and extracting that word is very different than returning all of the text after a specific character.

If all you want to do is return the text after the hyphen, then this should work:

=MID(A1,FIND("-",A1)+2,LEN(A1))

The MID function has three arguments:

=MID (text, start_num, num_chars)

The FIND function is used as the start_num argument. FIND returns the position of the hyphen in A1. We then add 2 to that to get past the space to the first letter of any text that follows the hyphen and space.

The LEN function is used as the num_chars argument since we don't know many characters to extract. We know the number of characters can't be higher than the total number of characters in the cell, so LEN(A1) covers all situations. If you know the maximum number of characters that the extracted text will ever be, you can just use that number.

e.g.

=MID(A1,FIND("-",A1)+2,15)

On the other hand, if the original string will always start with "Complete Draft - " then we don't have to FIND the hyphen, since it will always be in the same place:

=MID(A1,18,LEN(A1))

or

=MID(A1,18,15)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question