Find and Replace a * !!! AArghhh!!! 1

[rfr100]

Hi All.

We are moving a large amount of data between two ERP systems. The system the data is going into really dislikes *(asterisks !)

When I take the data out of the first system, i need to use Excel 2002 to find and replace all * in the data.

You probably dont need to think to long as to what happens if you do a find and replace * with nothing.

Hooray - NO MORE DATA !!!!

How can I do this ?

Cheers in advance

Rob

 

[mgrigsby]

Rather than searching for * try prefixing the * with ~ so you search for ~*

It works in Excel 2000 so hopefully should do the same in 2002.

Margaret

 

[rfr100]

Excellent, thanks.

Been trying to work that out for about an hour.

Im pretty sure that was the only character I didnt try !!!!!

I love tildes ~~~~~~~

Thanks again,

Rob

 

[SkipVought]

hey rfr,
Here's a way...

Sub ReplaceAsterisk()
    Dim rng As Range, vByte, i As Byte, sOut As String
    Set rng = ActiveSheet.UsedRange.Cells(1, 1)
    If Not rng Is Nothing Then
        firstAddress = rng.Address
        Do
            Set rng = ActiveSheet.UsedRange.Find(What:="*", After:=rng, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
            sOut = ""
            For i = 1 To Len(rng.Value)
                vByte = Mid(rng.Value, i, 1)
                If vByte <> &quot;*&quot; Then
                    sOut = sOut & vByte
                End If
            Next
            rng.Value = sOut
        Loop While Not rng Is Nothing And rng.Address <> firstAddress
    End If
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com