Excel VBA search and replace a string

I need a code to search dashes - into a string and if found then replace it with space

how I can do that?
LVL 6
FloraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NorieConnect With a Mentor VBA ExpertCommented:
You'll need to use Replace twice.
Dim strAString As String

    strAString  ="A-test-_string__"

    MsgBox Replace(Replace(strAString, "-"," "), "_", " ")

Open in new window


If you had  lot of different characters you want find/replace you could use an array.
Dim strAString As String
Dim arrChars()
Dim I As Long

    arrChars = Array("-", "_", "?")
    strAString = "A-test-_string__"
    For I = LBound(arrChars) To UBound(arrChars)
        strAString = Replace(strAString, arrChars(I), " ")
    Next I
    
    MsgBox strAString

Open in new window

0
 
Haris DjulicCommented:
Use this formula

=+SUBSTITUTE(A1;"-";" ")

Where A1 iscell with your data
0
 
FloraAuthor Commented:
I know Subs  formula.  my question is on how to use the vba built in function "Replace"  in VBA not in Built in function of Excel
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Haris DjulicConnect With a Mentor Commented:
Here is the VBA syntax

Worksheets("SHEET_NAME").Columns("A").Replace What:="-", Replacement:=" ", SearchOrder:=xlByColumns, MatchCase:=True

Open in new window

0
 
NorieVBA ExpertCommented:
This is how you would use replace.

Dim strAString As String

    strAString  ="A-test-string"

    MsgBox Replace(strAString, "-"," ")

Open in new window

0
 
ProfessorJimJamConnect With a Mentor Commented:
Replace(yourstring, "-", " ")
0
 
FloraAuthor Commented:
thank you guys

last query on this,   if I want to replace dashes and underscores - & _ with space
how can this be done?  does it require two times replace or ampersand can be used in replace?
0
 
Haris DjulicCommented:
two times replace..
0
All Courses

From novice to tech pro — start learning today.