Hi,

Assume a column of names as follows:

Name
Mohammed Zia-Ul Haque
Steven Thomas -
,-Rohit Sunil Ahir-Chowdhary.-
Anuj -----------
Sameer --
..,Mohit --
Rajeev Nair.
Monalisa . Das
Vijeta ...
--,.Anjana. M.U..,-

Please observe that there are special characters before the name, within the name and after the name.  The task is to remove special characters before and after the name.  The expected result is shown below:

Expected Result
Mohammed Zia-Ul Haque
Steven Thomas
Rohit Sunil Ahir-Chowdhary
Anuj
Sameer
Mohit
Rajeev Nair
Monalisa . Das
Vijeta
Anjana. M.U

The array formula (Ctrl+Shift+Enter) to make this work is

=MID(A2,MIN(SEARCH(CHAR(ROW(\$A\$65:\$A\$90)),A2&CHAR(ROW(\$A\$65:\$A\$90)))),LOOKUP(2,1/((CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=65)*(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=90)),ROW(INDIRECT("1:"&LEN(A2))))-(MIN(SEARCH(CHAR(ROW(\$A\$65:\$A\$90)),A2&CHAR(ROW(\$A\$65:\$A\$90)))))+1)

I have solved a similar problem at this link as well but that requires the usage of an add-in.  This is so because the special characters and numbers need to be removed from within the string as well.  In other words, everything except letters need to be removed from the alphanumeric string (no matter where the numbers and special characters are - beginning, middle or at the end).

1. This array-entered** formula is approximately half the length of the one you posted at that link and it appears to return the same results...

=MID(LEFT(A2,MAX(IF(ABS(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-77.5)<13,ROW(INDIRECT("1:"&LEN(A2)))))),MIN(IF(ABS(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-77.5)<13,ROW(INDIRECT("1:"&LEN(A2))))),99)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note that the above formula assumes that the last letter will always be at position 99 or less... if the text could be longer, then just change the 99 in the formula to a number that will always be larger than the position number of the last letter.

• Hi,

Thank you for sharing this shorter formula.

2. Hi,

Is there a video for the above formula? Not sure how to dissect as it's nested and evaluating won't help me.

• Hi,

No, I do not have a video. If you can share your data and the expected result, may be a simper formula can be written.