• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Flag special characters

F

forea

New Member
I have a list of names that I need to ensure are only with any or all of the following:
- Upper case
- Lower case
- Numbers
- Special characters: comma, period, forward slash, hyphen, apostrophe
Anything apart from this, I need to flag or preferably, not allow the user to enter.
Can anyone help?
 
Hi ,

It doesn't work because your language is not English , and you never mentioned this in your original post.

Narayan
 
I think I understand what you are looking for but to be certain:
Please select one of the lines in your text.xlsx file (Let's say row 2, insert a new row 3 below row 2 (blank)), then correct the contents of row 2 and put it in row 3.
 
Narayan, I did specify what I required but I guess it was too vague for you to understand.
Eloise, appreciate your help as well (not the sarcasm), but I don't think manually changing over 5000+ lines is a good use of my time.
 
No sarcasm. I simply want to see a "before" and "after" so I (and anyone else reading this) understands what you're wanting to accomplish. Your original e-mail is not very clear.
 
You may have already figured this out, but the PROPER function will do a lot of what you are requiring.
http://www.excelfunctions.net/Excel-Proper-Function.html

Additionally, you may want to simply create a formula that checks for ß and replaces it with ss, and the same for ö to o and any others you want to "fix" including Russian characters, et. al. You'll simply add the "offending" character with its desired replacement as you encounter them. You end up with one LONG formula containing all the offending characters and their replacements.
e.g. =PROPER(TRIM(SUBSTITUTE(A2,"ß","ss")))
http://www.excelfunctions.net/Excel-Substitute-Function.html
http://www.excelfunctions.net/TrimFunction.html

Simply compound this formula to include the other characters for which you want to check and drag the formula to cover those cells.
=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"ß","ss"),"ö","o"),"в","u"),"с","s"),"т","t"),"р","r"),"и","I"),"я","a")))

The "corrected data" can be copied to wherever you desire. See attached spreadsheet.

One more idea. You could use VLOOKUP and create a table of the offending characters with their replacements.
http://www.excelfunctions.net/ExcelVlookup.html
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top