Help! Corrupted data needs solving!

Is there a way to match up the characters in column C to column B, even though the characters are not in the right order in column B?

A wildcard or a find and replace perhaps?

Please see example attached.

Thanks in advance!

Please post a small sample file.

All you need to do is put in a formula that removes everything before the space in your column B and then use that to find your name in column C with a VLOOKUP.

E.g.
=RIGHT(B2,LEN(B2)-FIND(" ",B2))

Find the Formula 1.xlsx

Please see attached.

Thanks both, attached above is a bit more explanation.

jwmanners - tried =RIGHT(B2,LEN(B2)-FIND(" ",B2)) but don't think I explained fully, so not quite what we need.

It is not clear, anyway try this
In D2 , then Dragged down
=SUBSTITUTE(C2," ","")

Perfect!

Sorry I wasn't clearer.

Thank you kvsrinivasamurthy!

Capture2.JPG

On reflection, sorry this is again not what we are after. I have explained further in the attached screenshot.

I hope this is more clear.

This is a sample of our actual data. We have replicated your formatting. This includes some contacts that may not be in column B and therefore wont match up on rows when sorted so we are still returning the wrong values.

Thanks for taking the time to help us

Capture3.JPGFormula.xlsx

Pl upload file. Screen shot will not help. Explain your problem with example.

In your previous "Find The formula.xlsx" file the entries in B do not align with the results in C.

61147 MonkiaZysk Anthony Zybutz

I would have expected the following

61147 MonkiaZysk Monkia Zysk

The following reproduces your column C as per your first file:

=IFERROR(IF(MATCH("*"&LEFT($C2,FIND(" ",$C2)-1)&"*",$B$2:$B$8,0),$C2),IFERROR(IF(MATCH("*"&MID($C2,FIND("*",SUBSTITUTE($C2," ","*",1),1)+1,FIND(" ",SUBSTITUTE($C2," ","*",1),1)-FIND("*",SUBSTITUTE($C2," ","*",1),1)-1)&"*",$B$2:$B$8,0),$C2),IF(MATCH("*"&MID($C2,FIND(" ",$C2)+1,255)&"*",$B$2:$B$8,0),$C2)))

It searches B for Matches against the "component" names in C

However I would have thought it was required to "deconstruct" B i.e change "MonkiaZysk" to "Monkia Zysk" then search C for matches against B (?).

See the attached.

Columns F to I finds the position of the capital letters in data in B. Columns J to L are the names in B separated out.
in I2

=SMALL(FIND(0,SUBSTITUTE($B2,CHAR(ROW(INDIRECT("65:90"))),0)&0),F$1)

Enter with Ctrl+Shift+Enter

Repeat for G, H and I then copy down

In J2

=MID($B2,F2,G2-1)

Copy across and down

in E2

=IFERROR(INDEX($C$2:$C$8,MATCH("*"&J2&"*",$C$2:$C$8,0)),IFERROR(INDEX($C$2:$C$8,MATCH("*"&K2&"*",$C$2:$C$8,0)),INDEX($C$2:$C$8,MATCH("*"&L2&"*",$C$2:$C$8,0))))

Searches for match on each name until found.