MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replacing Characters within a String

Posted by Christopher Patrick on January 16, 2002 9:24 AM

Hi there everyone-

I was wondering if anyone can help with this problem. I have a string variable, and want to replace/delete one character in the string. The string I am reading in contains a space at the end of the string, and I am looking to remove the space, or replace it with a null. Is this possible?

The string is in the format: "ABC ", and I wish to make it "ABC".

Thanks in advance for any help


Posted by Mark W. on January 16, 2002 9:33 AM

=TRIM("ABC ") (nt)

Posted by Nate Oliver on January 16, 2002 12:53 PM

Two Ways: Formula & VBA

Two ways:

1) Formula: =SUBSTITUTE(A1," ","")

2) VBA:

Sub replacer()
thename = WorksheetFunction.Substitute(Range("a1"), " ", "")
Range("a1") = thename
End Sub

Cheers! Nate

Posted by Christopher Patrick on January 17, 2002 7:46 AM


Thanks to both of you...I am using both solutions and they work great!