Search code examples
excelexcel-formulaexcel-2010excel-2007

How to remove unwanted commas and hyphenate between words in Excel?

I have a huge column of data and I need to edit and remove the commas, brackets and hyphen in column 1 and edit it as given in column 2 below.

example

column1

airline,-airport-and-aviation-management-(including-foundation-year)---bsc-(hons) airline,-airport-and-aviation-management---bsc-(hons)

The exected result should be as below 

   column2 
    
    airline-airport-and-aviation-management-including-foundation-year-bsc-hons
    airline-airport-and-aviation-management-bsc-hons
Solution

  • Sheet couple of soln(s) for you (here/screenshot below) refers

    Excel function

    This is using:

    VB function

    Function Compress(str As String, Optional BadChars As String = ",#.-&+@'~`[]{}<>/\|()")
##Remove default and custom chars..
##Adapted by 007-JB (01 07 21)
    Dim Ch As Long
    If Len(BadChars) <> 0 Then
        For Ch = 1 To Len(BadChars)
            str = Replace(str, Mid(BadChars, Ch, 1), "")
        Next Ch
    End If
    Compress = str
End Function