Excel - Extracting substring after a character contained in a string

Question

I am trying to extract partial string contained within a cell after a certain character in the string.

I have a formula which can do this

=RIGHT(C10,LEN(C10)-SEARCH(":",C10))

But let's say the location of C10 cell isn't exactly known within the C column, and a way to locate it is by searching for a keyword in column D and then extracting the partial string using the above formula to a cell next to the keyword (column E).

I hope this makes sense.

Ex)

Column C
John: 1234 Alphabet Street 
Claire: 3456 Diamond Street
Tim: 333 Laugh Lane
Bo: 5555 Great Neck Street
Grace: 777 Whiteside Blvd

Column D
John:
Claire:
Bo:

Thank you.

Answer 1

This approach should work for you. First, modify the formula as below for the first row in E (i.e., E2)

=IF(ISTEXT(D2),RIGHT(C2,LEN(C2)-SEARCH(":",C2)),"No address found.")

In order to apply this formula throughout for the column triplets C, D and E do the following: Click on the top-left corner of column 'C', hold and drag the cursor towards right to include columns D and E and now drag all the way down to the last row of C, D and E. Now, the formula is automatically applied to all the rows viz. C, D and E.

Answer 2

You could use:

=TRIM(SUBSTITUTE(VLOOKUP(D1&"*",C:C,1,0),D1,""))