Find / Replace Macro

analysis_paralysis

New Member
Joined
Mar 12, 2009
Messages
16
I have to do a highly repetative find / replace action on a very large table every single day. Any help for a macro would be appreciated. I have looked through my references and I am coming up blank.... as I said in my other request, my brain is not firing on all cylinders tonight....
Help!? :)
A_P
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Some questions to clarify:

What's the find/replace?
Is it multiple replacements in a single field?
Does it cover multiple fields?
Is the data imported every day, requiring a cleanup? If so, can anything be done at the source?

Are you extracting numbers from text, text from numbers, stripping unwanted characters, or remapping data?

Denis
 
Upvote 0
Thank you for response.
To clarify:
there are multiple replacements across 2 different fields.
I have to replace letters from a field that I only want numbers in and remove slashes from another field and convert YYYY to YY in a date that is represented as text.
The data is imported every day and I am not able to control it - it is a foxpro db that I don't have access to other than the linkage.
I am removing text from numbers (and in a sense numbers from text in that I also need to remove characters from a field that was a date field and I have to convert to txt.) and stripping unwanted characters.
Thanks so much!
 
Upvote 0
Here's how to strip the text characters from a mixed field:
http://www.mrexcel.com/forum/showpost.php?p=1814217&postcount=7

To do the dates it will depend what format you are given.
Assuming that it's a field called MyDate, laid out like MM/DD/YYYY (as text) and you want MM/DD/YY (as a date) you could do something like:
DateSerial(Right([MyDate],4),Mid([MyDate],4,2),Left([MyDate],2))

The first step would be to create a standard query and check that the fields turn out right. Once you have that, you can convert that to an Update query which will clean your data in one step.

Denis
 
Upvote 0
Thank you so much for you help.
Question regarding the link, this looks like it is for stripping non alpha from a string while I wan to strip the letters leaving only numbers from the string.
What woudl I need to change, I haven't been able to find the info.
 
Upvote 0
Here is the code from the post, including a command to strip alpha characters.

Code:
Function StripNonAlpha(strInput As String) As String
    Dim reg As RegExp
    
    Set reg = New RegExp
    reg.Global = True
    reg.IgnoreCase = False
    'first replacement, most non-alphas
    reg.Pattern = "\W"
    strInput = reg.Replace(strInput, "")
    'second replacement, underscores
    reg.Pattern = "_"
    strInput = reg.Replace(strInput, "")
    'third replacement, alphas
    reg.Pattern = "[a-zA-Z]"
    StripNonAlpha = reg.Replace(strInput, "")
End Function
Note: this should work but I will go for a dig to give the reverse; instead of 3 processes to remove any non-numbers, one call to just return numbers. Back when I find it...

Denis

 
Upvote 0
This is adapted from a few answers on the Board. I've annotated it to indicate what is happening...

Code:
Function GetNumbers(strInput As String)
    Dim temp As String
    Dim i As Long
    Dim oMatches
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "\d+" '\d means all integers. ^\d means EXCLUDE all integers
        If .Test(strInput) Then 'if numbers then proceed
            Set oMatches = .Execute(strInput) 'result is an array
            For i = 0 To oMatches.Count - 1
                temp = temp & oMatches(i)
            Next i
            GetNumbers = temp
        End If
    End With
End Function

This gives the same result as the earlier function but does it in one pass, so it should be quicker ofr large numbers of records.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top