MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Replace Method

  Asked By: Lourdes    Date: Dec 05    Category: MS Office    Views: 925

Here is one problem I've stuck with, need a solution for that, hope
that something will come out of box :-p
Here is the problem
The values in the cell can come like "9999 999999 9999999 XYZ", and
I need to remove all 9999 999999 9999999 and a space after that so
the final result will be in the cell is only "Anjan" with no space.

Here is the code that I've used.
ActiveSheet.Columns("A").Replace What:=sTextToFind,
Replacement:="",SearchOrder:=xlByColumns, MatchCase:=True

Where sTextToFind = "9999 999999 9999999 "

But, this code is retruning me " XYZ" in place of "XYZ"
I've also, used VbNullString in place of "", still the same

Thanx in advance, also wish u all a very Happy New Year for the
coming year



3 Answers Found

Answer #1    Answered By: Code Guru     Answered On: Dec 05


This should remove any leading spaces.

Answer #2    Answered By: Qadriyyah Malik     Answered On: Dec 05

'Solution assumes the original string is in $A$2 in a worksheet
'named "StringsToParse", and the result is returned to $A$3.
'Also assumes that the last alpha substring in the original string
'is what needs to be returned.
'This solution  does not require use of the TRIM function.
'It can be modified to iterate any number of cells.
'It can be modified to pull multiple alpha substrings from the
'original string to be concatenated together or otherwise handled.
'Note the simple use of the powerful Regular Expressions object.
Option Explicit

Public Sub RemoveNonAlphaFromString()
'Must remove all substrings and spaces except the alphabetic portion.
Dim strOriginal, arrSplit, ctrArray, strFinal, strReturned,
strOriginal = Worksheets("StringsToParse").Range("$A$2")
arrSplit = Split(strOriginal, Chr(32), -1, vbTextCompare)
For ctrArray = 0 To UBound(arrSplit)
strSubString = arrSplit(ctrArray)
strReturned = TestStringForAlpha(strSubString)
If strReturned = strSubString Then
'Success. This is the alpabetic substring.
Worksheets("StringsToParse").Range("$A$3") = strReturned
'Failure. This is an unneeded substring.
'Do nothing. Iterate array for next substring.
End If
End Sub

Public Function TestStringForAlpha(strSubString)
Dim objRegExp
Set objRegExp = CreateObject("VBScript.regexp")
With objRegExp
.IgnoreCase = True
.Pattern = "[A-Z]{3}"
.Global = True
If .Test(strSubString) = True Then
TestStringForAlpha = strSubString
'This returned fail code here must not be able
'to match any legitimate substring.
TestStringForAlpha = "99Fail"
End If
End With
End Function

Answer #3    Answered By: Terry Williams     Answered On: Dec 05

It's fine when I try it (Excel 2003).

I notice that you haven't copy/pasted EXACTLY what you have in your VBA
environment (for instance, there is no space after the comma before
"SearchOrder"), so I can't run your precise code. However, I did copy the
two statements out of the e-mail and into the VBE and they worked fine.

Before looking for other causes, you should suspect that it's something that
you are doing wrong. Things to look for would be double spaces in your
cells and a missing final space in the sTextToFind constant.

You might like to do an exact copy/paste of a few cells and of the code.

Didn't find what you were looking for? Find more on Replace Method Or get search suggestion and latest updates.