Replace multiple words within a string

DemonForce
DemonForce used Ask the Experts™
on
Hi,

I have A1 = Welcome <username> today is <date> and you are using version <version>

Now I want cell b2 to equal sell a1 but replace username date and version with other data, now I know how to use substitute to do this, but some of the data I need ones deep within routines I have, so therefore need a vba version which as a function basically does the same thing.

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SteveCost Accountant
Top Expert 2012

Commented:
VBA would use REPLACE as you would SUBSTITUTE
Top Expert 2014

Commented:
.
How about this:
Public Sub substitute(ByRef s As String, ByVal magic As String, ByVal content As String)
    substitute = Replace(s, magic, content)
End Sub

Open in new window

Just call it for every magic to be replaced, i.e.
(...)

Dim str as String
str = Range("A1").Value
str = substitute(str, "<username>", "Joe Miller")
str = substitute(str, "<date>", "2014/02/26")
str = substitute(str, "<version>", "1.0.3.2.0 pre-alpha prototype")

(...)

Open in new window

Jeremy TyreSystem Project Analyst

Commented:
If I understand this correctly, you might be able to handle this with REPLACE in excel.

Syntax: REPLACE( old_text, start, number_of_chars, new_text )

http://office.microsoft.com/en-us/excel-help/replace-values-HA104054000.aspx
Jeremy TyreSystem Project Analyst

Commented:
@DemonForce  Any updates on this?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial