CODEWITHSUNDEEP
excel
kamal de silva
kamal de silva

Reputation: 43

how to remove "-" and "/" characters from excel sheet

I want to remove "-" and "/" from 011-2729729 011/2729729 these numbers and convert them in to 0112729729 in excel. I tried with substitute function but i could not get the correct answer.

Already attempted formula: =SUBSTITUTE(A1,"/"," ",4)

Upvotes: 1

Views: 104

Answers (2)

chancea
chancea

Reputation: 5968

If you truly desire a formula over a macro use this, else Gary's Student provided a nice macro.

If you are specifically searching position 4 then use REPLACE not SUBSTITUTE with a simple IF check at the start to see if position 4 is a "/" or "-"

=IF(OR(MID(A1,4,1)="/",MID(A1,4,1)="-"),REPLACE(A1,4,1,""),A1)

Notes:

  • SUBSTITUTE is great when you want to replace certain text with other text
  • REPLACE is great when you want to replace a certain position with other text

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96773

Sometimes removing "/" and "-" will create a string that Excel will treat as a number and you can lose leading zeros. This small macro will fix the cells "in place"

Sub FixValues()
   Dim r As Range, v As String

   For Each r In ActiveSheet.UsedRange
      v = r.Text
      If InStr(v, "-") > 0 Or InStr(v, "/") > 0 Then
         r.NumberFormat = "@"
         r.Value = Replace(Replace(v, "-", ""), "/", "")
      End If
   Next r
End Sub

Upvotes: 1

Related Questions