CODEWITHSUNDEEP
excelexcel-formula
NvdB31
NvdB31

Reputation: 312

How to align columns that contain matching string in Excel?

I'm struggling with this simple Excel formula because my knowledge is too limited. Hours of Google didn't get me the exact thing I need. Which is actually very simple:

I have one column with website URL's (column B) And I have one column with e-mail addresses. (Column D)

However, because the two columns came from different sources they are not aligned. So the email addresses are not horizontally aligned with their belonging web address.

I need a formula which takes the domain name of email address and looks for that part of the string in the URL column, and then return the e-mailaddress in column C, nicely aligned to the corresponding web address in Column B.

Upvotes: 0

Views: 969

Answers (1)

user4039065
user4039065

Reputation:

This is a surprisingly simple formula once wildcards are introduced to a MATCH function.

      enter image description here

The standard formula in C2 is =INDEX($A$2:$A$5,MATCH("*"&REPLACE(B2,1,FIND("@",B2),"")&"*", $A$2:$A$5,0)).

Upvotes: 2

Related Questions