[ms office - 2013] [Excel 2013] Data Restrictions

[CHiLL]

Hello, I don't know if this is possible so I'm wondering if someone can let me know!

 

I have a spreadsheet containing new users' forenames, surnames and intake year. I would like to ensure that when using this data to creating user accounts, no spaces or special characters are included, such as from those users with double-barrelled names for example.

 

If I have a user named Christopher Johnson-Smith, can Excel remove that hyphen or have rules (or something) in place so that if a hyphen is entered, it is then removed?

 

Like I said, I have no idea if this is possible!

Any help would be great.

Edited June 25, 2015 by CHiLL

[Seb1780]

You can use the FIND function to locate these characters in a string, it will return the position in the string of the character you are looking for. You could then use other string functions (LEN, LEFT, RIGHT) to manipulate the string to remove the hyphen or whatever.

 

See Using Excel's Find and Mid to extract a substring when you don't know the start point - TechRepublic for examples of how to do this.

 

You will need to use FIND for each character you wish to remove, so this could get very long winded.

 

EDIT: You could also use the "Text to Columns" feature to split the names by defining the undesirable characters as the delimiters then use CONCATENATE or & to join them back into one string.

Edited June 25, 2015 by Seb1780

[supawat1]

Without thinking too complicated, why do not you use Find and Replace function?

[pcstru]

You could use the SUBSTITUTE function to remove any characters you don't want. Just pass it an empty string for the character to replace the hyphen with.

 

Is there a reason for mangling the hypen? It is not exactly uncommon and most applications will cope.

[AlbertHdez]

The simplest way is to use the SUBSTITUTE function. As an example: hyphenated name is on column A enter the following formula in an adjacent column: =SUBSTITUTE(A1,"-"," ",1)