Forum Discussion
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 - ...
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.
Now i fully understand. Thank you so much sir😊