Forum Discussion
Excel
Feb 13, 2021Iron Contributor
Question related to finding duplicates
Hello Everyone, How to find the duplicate values When the data of First column with First Name ,Middle Name, Last Name and the other column with Last Name, Middle Name, First Name Like - ...
HansVogelaar
Feb 13, 2021MVP
SUBSTITUTE(B2," ",REPT(" ",255)) replaces each space in the name with 255 spaces.
So for instance
"Murthy Krishna Murali"
will be changed to
"Murthy (255 spaces) Krishna (255 spaces) Murali".
RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255) takes the last 255 characters of this string. It will look like
" (lots of spaces) Murali"
TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255)) removes the leading spaces, so that you end up with
"Murali"
By placing this at the beginning of the formula, we move the last part to the front.
Similarly, TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255,255)) extracts the middle name, and TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255)),255)) extracts the first part.
Excel
Feb 14, 2021Iron Contributor
Now i fully understand. Thank you so much sir😊