CODEWITHSUNDEEP
excelvbamacos
larin555
larin555

Reputation: 1699

Excel Find and replace using variables

I'd like to know if there's a way to FIND AND REPLACE in this way :

I need to find all cells that contains apt followed by a number. But the number is always different. So is there a script or something that would do that?

FIND : apt XX (the 2 Xs representing the numbers)

REPLACE BY : app 30

So it would locate all the places where apt + a number

Because I have thousands of lines, and I don't want to do :

FIND : apt 01...

FIND : apt 02...

FIND : apt 03...

and so on

Thank you!

Upvotes: 0

Views: 5541

Answers (4)

Anoop
Anoop

Reputation: 1

Find 'apt ???' Replace 'apt 30'

Find 'apt ??' Replace 'apt 30'

You could use below if you want to replace everything after apt: Find 'apt*' Replace 'apt 30'

Upvotes: 0

martin
martin

Reputation: 2648

RegEx are indeed useful, but I believe for this the built-in 'regex' is good enough. From the help file:

Use the asterisk to find any string of characters. For example, s*d finds "sad" and "started".

Use the question mark to find any single character. For example, s?t finds "sat" and "set".

So in your case just press Ctrl+H to get Search And Replace dialogue, then write apt * into search box and app 30 into replace box.

PS: It is not clear from your question whether there are some cells that contain apt followed by something else than a number and that should be preserved. If this is the case, go with RegEx, either as Emmanuel suggests or by using Scripting.Regex in VBA.

Upvotes: 1

jrad
jrad

Reputation: 3190

Here's what you are looking for:

Sub findApt()
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("B:B"))
    Dim i As Integer
    i = 1
    Do While i <= count
        If (InStr(Range("B" & i), "apt")) Then
            Range("B" & i) = Mid(Range("B" & i), 1, Application.WorksheetFunction.Search("apt", Range("B" & i)) - 1) & "app 30"
        End If
        i = i + 1
    Loop
End Sub

This assumes that your data is in column B. Enjoy!

Upvotes: 2

Emmanuel N
Emmanuel N

Reputation: 7449

I would recommend you to install Find And Replace Excel Regex. Allows you to use regular expression to find and replace.

Upvotes: 0

Related Questions