Force Removal of spaces on both sides of any dash/hyphen in an entire column in Excel

  • How can I force the removal of spaces on both sides of a dash/hyphen for an entire column in Excel? Ideally, I'd like any added value to be trimmed in this manner automatically. Having to apply a formula or similar to the column after the fact won't work. I am looking to auto correct (or as a secondary option, highlight) in real time.

    Problem: Non technical users add values to a column in a data sheet and they are inconsistent with their spacing around dashes (where consistency is needed). Thanks.

    Column 1

    Car-SUV-Blue - 2020 will be Car-SUV-Blue-2020

    Car -SUV-Blue- 2020 will be Car-SUV-Blue-2020

  • How about preventing it instead of correcting it? You can do it with data validation.

    1. Select cell A1
    2. Highlight the entire column
    3. Enter the following custom data validation equation:

    =AND(ISERROR(FIND(" -",A1)),ISERROR(FIND("- ",A1)))

    Does that work for you?

    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

  • Put a Data Validation rule that prevents such entry.

    Select the range where is it to apply, do Data Validation, Custom, and use this (replacing F1 with the reference of the first cell of your selection):

    =ISERROR(SEARCH(" - ",F1))

    Put a suitable message in the Error Alert.

    EDIT: mdenino beat me to it, and is more comprehensive

    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!