Using MID with IF statement

[dshim86]

Hi my 1st time posting here and look forward to all your support and help!

I am trying to use MID from Column A1 onto B1 and then restrict/data validate Column B by preventing special characters. I want it to return in column B the MID function result but if it contains special characters it should return error message "Special characters not allowed", if no special characters just return the results of the MID function?

Column A Column B
ABC123/D

 

[etaf]

I found this formula to count special characters in a cell string

https://superuser.com/questions/660692/is-there-an-excel-formula-to-identify-special-characters-in-a-cell
=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>45))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>95))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122)*1)

So that could be used in an IF statement to test for 0 = NO special characters
it returns ref if blank cell

=IF( (SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>45))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>95))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122)*1))=0, MID() , "")

or if you know all the special characters you want to restrict you could use
https://www.excelforum.com/excel-general/565027-return-true-if-cell-contains-special-character.html
=SUMPRODUCT(--(ISNUMBER(FIND({"*","/","?"},A1))))