Find and Replace variable string

[mfaerber]

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters + ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

 

[Rick Rothstein]

We need a little more detail to be sure how to respond to you. The cells
with your codes...

1) (a) Are they located in a specific column or set of columns?
(b) Or are they located in a specific row or set of rows?
(c) Or can they be located in any cell anywhere within the worksheet.

2) You said you have "Excel spreadsheets"... plural... are those multiple
worksheets in the same workbook (meaning the code we give you has to iterate
the worksheets)?

3) Are these codes the only thing in the cell containing them or, besides
the text you want us to add, could they be surrounded by other text within
that same cell?

Rick Rothstein (MVP - Excel)




"mfaerber" wrote in message

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters + ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

 

[mfaerber]

We need a little more detail to be sure how to respond to you. The cells
with your codes...

1) (a) Are they located in a specific column or set of columns?
     (b) Or are they located in a specific row or set of rows?
     (c) Or can they be located in any cell anywhere within the worksheet.

2) You said you have "Excel spreadsheets"... plural... are those multiple
worksheets in the same workbook (meaning the code we give you has to iterate
the worksheets)?

3) Are these codes the only thing in the cell containing them or, besides
the text you want us to add, could they be surrounded by other text within
that same cell?

Rick Rothstein (MVP - Excel)

Thanks Rick - sorry the first post was less than specific, I'll
clarify:
1) The codes are always in one specific column (so I plan on just
selecting the range as the column, then trigger the macro).

2) This could be set up to work with just the current sheet, does not
have to iterate thru entire workbook

3) Contents of the cell vary -- sometimes just a code, sometimes
blank, sometimes some other text. But there is NEVER an example of a
code plus some other text, the code is the only thing there if it is
present!

Hope this clarifies it better, thanks!

 

[Ron Rosenfeld]

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters + ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed. Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.


===========================
Option Explicit
Sub FindReplace()
Dim c As Range
Dim re As Object
Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = False
.Pattern = sPat
End With

For Each c In Selection
c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

 

[mfaerber]

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed.  Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.

===========================
Option Explicit
Sub FindReplace()
    Dim c As Range
    Dim re As Object
    Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
    .Global = True
    .ignorecase = False
    .Pattern = sPat
End With

For Each c In Selection
    c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

Fantastic -- thanks so much for the speedy response and expertise,
Rick! Works great in my first sample test!
I've got a couple variations to try to play with and incorporate, plus
I want to look up some of this coding so I can better understand "how
it works" (like I said earlier, new to VB but want to get better), but
this gets me the core functionality I need.

Thanks again Rick... much, much appreciated!

 

[mfaerber]

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters +  ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed.  Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.

===========================
Option Explicit
Sub FindReplace()
    Dim c As Range
    Dim re As Object
    Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
    .Global = True
    .ignorecase = False
    .Pattern = sPat
End With

For Each c In Selection
    c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

Fantastic - thanks Rick for the speedy response and expertise! This
works great in my first sample test!
Now I want to play with and try to incorporate some variations, as
well as look up some of this code to better understand it (as I said
before I'm new to VB but want to get better), but this gives me the
core functionality I needed.

Thanks again Rick, much much appreciated!

 

[mfaerber]

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed.  Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.

===========================
Option Explicit
Sub FindReplace()
    Dim c As Range
    Dim re As Object
    Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
    .Global = True
    .ignorecase = False
    .Pattern = sPat
End With

For Each c In Selection
    c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

Fantastic - thanks Rick for the speedy response and expertise! This
works great in my first sample test!
Now I want to play with and try to incorporate some variations, as
well as look up some of this code to better understand it (as I said
before I'm new to VB but want to get better), but this gives me the
core functionality I needed.

Thanks again Rick, much much appreciated!

SORRY -- I meant "Thanks RON" for the code, as well as thanks again to
Rick!

 

[Rick Rothstein]

Okay then, I think this macro will do what you want...

Sub MakeCodeOld()
Dim Cell As Range
On Error Resume Next
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
If Cell.Value Like "[A-Za-z]##########" Or Cell.Value Like _
"[A-Za-z][A-Za-z]##########" Then Cell.Value = Cell.Value & " (old)"
Next
End Sub

Rick Rothstein (MVP - Excel)



"mfaerber" wrote in message

We need a little more detail to be sure how to respond to you. The cells
with your codes...

1) (a) Are they located in a specific column or set of columns?
(b) Or are they located in a specific row or set of rows?
(c) Or can they be located in any cell anywhere within the worksheet.

2) You said you have "Excel spreadsheets"... plural... are those multiple
worksheets in the same workbook (meaning the code we give you has to
iterate
the worksheets)?

3) Are these codes the only thing in the cell containing them or, besides
the text you want us to add, could they be surrounded by other text within
that same cell?

Rick Rothstein (MVP - Excel)

Thanks Rick - sorry the first post was less than specific, I'll
clarify:
1) The codes are always in one specific column (so I plan on just
selecting the range as the column, then trigger the macro).

2) This could be set up to work with just the current sheet, does not
have to iterate thru entire workbook

3) Contents of the cell vary -- sometimes just a code, sometimes
blank, sometimes some other text. But there is NEVER an example of a
code plus some other text, the code is the only thing there if it is
present!

Hope this clarifies it better, thanks!

 

[Rick Rothstein]

SORRY -- I meant "Thanks RON" for the code, as well as thanks

again to Rick!

If you check our sub-thread, you will see an alternate macro you can
consider using as well...

Rick Rothstein (MVP - Excel)

 

[Ron Rosenfeld]

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters +  ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed.  Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.

===========================
Option Explicit
Sub FindReplace()
    Dim c As Range
    Dim re As Object
    Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
    .Global = True
    .ignorecase = False
    .Pattern = sPat
End With

For Each c In Selection
    c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

Fantastic - thanks Rick for the speedy response and expertise! This
works great in my first sample test!
Now I want to play with and try to incorporate some variations, as
well as look up some of this code to better understand it (as I said
before I'm new to VB but want to get better), but this gives me the
core functionality I needed.

Thanks again Rick, much much appreciated!

SORRY -- I meant "Thanks RON" for the code, as well as thanks again to
Rick!

You're welcome. Glad to help.

Note that Rick's macro assumes that the only thing in your cell is the string; whereas mine assumes that your cell contains the string, but might also contain other data. That difference may or may not be a problem. Also Rick did not confine his pattern to just uppercase letters, but that is an easy (and obvious) change.

To change my regex so as to ensure that the cell contains ONLY the string, I would change sPat to:

Const sPat = "^([A-B]{2}\d{10})$"

 

[Rick Rothstein]

Note that Rick's macro assumes that the only thing

in your cell is the string; whereas mine assumes that
your cell contains the string, but might also contain
other data.

Ron,

In a different sub-thread, I asked the OP three questions and these were his
responses to me...

1) The codes are always in one specific column (so I plan on just
selecting the range as the column, then trigger the macro).

2) This could be set up to work with just the current sheet, does not
have to iterate thru entire workbook

3) Contents of the cell vary -- sometimes just a code, sometimes
blank, sometimes some other text. But there is NEVER an example of a
code plus some other text, the code is the only thing there if it is
present!

My assumption was based on the last part of #3's answer. As for the lower
case text... I didn't feel like asking another question and losing time, so
I just guessed at that one.<g>

Rick Rothstein (MVP - Excel)

 

[Ron Rosenfeld]

Ron,

In a different sub-thread, I asked the OP three questions and these were his
responses to me...

1) The codes are always in one specific column (so I plan on just
selecting the range as the column, then trigger the macro).

2) This could be set up to work with just the current sheet, does not
have to iterate thru entire workbook

3) Contents of the cell vary -- sometimes just a code, sometimes
blank, sometimes some other text. But there is NEVER an example of a
code plus some other text, the code is the only thing there if it is
present!

My assumption was based on the last part of #3's answer. As for the lower
case text... I didn't feel like asking another question and losing time, so
I just guessed at that one.<g>

Rick Rothstein (MVP - Excel)

Re-reading his first post, we both made some missassumptions.

I based the case of the letter on:

The codes follow a pattern as such: One or Two **uppercase** letters + ten digits

However, I neglected to account for the possibility of just ONE upper case letter.

 

[Ron Rosenfeld]

Hi all,

I have Excel spreadsheets where some cells contain a specific code
(string of data), and I want to simply go through the entire sheet
looking for those code/strings, and replace them with new values that
are made up of the original code plus other text. The codes follow a
pattern as such: One or Two uppercase letters +  ten digits

For example: in a cell, find value of "AB1234567890", and replace it
with "value: AB1234567890 (old)". In some cases, certain uppercase
letters would mean a slightly-different output, but essentially
similar.

No other text would be changed as part of this macro, so it's really a
find & replace using regex/pattern to define the find.

I was hoping that I could do this in VBA as a macro, so that I could
trigger it by selecting a range (column) and running it.

Am new to VB and having some trouble figuring this code out, if anyone
can help it would be greatly appreciated!!

TIA -

To do this with regular expressions in a VBA macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first SELECT the range to be processed.  Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Note that the regular expression below will EXCLUDE strings that are already followed by " (Old)", so you don't have to worry about processing a string twice.

===========================
Option Explicit
Sub FindReplace()
    Dim c As Range
    Dim re As Object
    Const sPat As String = "([A-B]{2}\d{10})(??!\s\(old\)))"

Set re = CreateObject("vbscript.regexp")
With re
    .Global = True
    .ignorecase = False
    .Pattern = sPat
End With

For Each c In Selection
    c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
==========================

In this case, you need to "select" the range to be processed, but if you have more specific requirements, they can be incorporated.

Fantastic - thanks Rick for the speedy response and expertise! This
works great in my first sample test!
Now I want to play with and try to incorporate some variations, as
well as look up some of this code to better understand it (as I said
before I'm new to VB but want to get better), but this gives me the
core functionality I needed.

Thanks again Rick, much much appreciated!

SORRY -- I meant "Thanks RON" for the code, as well as thanks again to
Rick!

Sorry, I neglected to account for the possibility of just ONE upper case letter followed by 10 digits, as I see you specified either one or two.

Again, we need only change the sPat line and, since I know see that the code will be the only thing in the cell, we can also use the simpler version, and I can correct a typo that has permeated all of the versions wherein I typed [A-B] which should have been [A-Z]

So, should be

Const sPat as String = "^([A-Z]{1,2}\d{10})$"

or, the entire macro:

======================================
Option Explicit
Sub FindReplace()
Dim c As Range
Dim re As Object
Const sPat As String = "^([A-Z]{1,2}\d{10})$"

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = False
.Pattern = sPat
End With

For Each c In Selection
c.Value = re.Replace(c.Text, "$1 (old)")
Next c

End Sub
=================================

 

[Rick Rothstein]

Ron pointed out in the other sub-thread that I missed where you said
*uppercase* letters, so here is my code modified to look only for those...

Sub MakeCodeOld()
Dim Cell As Range
On Error Resume Next
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
If Cell.Value Like "[A-Z]##########" Or Cell.Value Like _
"[A-Z][A-Z]##########" Then Cell.Value = Cell.Value & " (old)"
Next
End Sub

Rick Rothstein (MVP - Excel)




"Rick Rothstein" wrote in message

Okay then, I think this macro will do what you want...

Sub MakeCodeOld()
Dim Cell As Range
On Error Resume Next
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
If Cell.Value Like "[A-Za-z]##########" Or Cell.Value Like _
"[A-Za-z][A-Za-z]##########" Then Cell.Value = Cell.Value & " (old)"
Next
End Sub

Rick Rothstein (MVP - Excel)



"mfaerber" wrote in message

We need a little more detail to be sure how to respond to you. The cells
with your codes...

1) (a) Are they located in a specific column or set of columns?
(b) Or are they located in a specific row or set of rows?
(c) Or can they be located in any cell anywhere within the worksheet.

2) You said you have "Excel spreadsheets"... plural... are those multiple
worksheets in the same workbook (meaning the code we give you has to
iterate
the worksheets)?

3) Are these codes the only thing in the cell containing them or, besides
the text you want us to add, could they be surrounded by other text within
that same cell?

Rick Rothstein (MVP - Excel)

Thanks Rick - sorry the first post was less than specific, I'll
clarify:
1) The codes are always in one specific column (so I plan on just
selecting the range as the column, then trigger the macro).

2) This could be set up to work with just the current sheet, does not
have to iterate thru entire workbook

3) Contents of the cell vary -- sometimes just a code, sometimes
blank, sometimes some other text. But there is NEVER an example of a
code plus some other text, the code is the only thing there if it is
present!

Hope this clarifies it better, thanks!

 

[Rick Rothstein]

The codes follow a pattern as such: One or
Wow, it really did say that... and I completely read right over it.

Thanks for pointing that out (I just corrected my code to account for it).

Rick Rothstein (MVP - Excel)

 

[mfaerber]

Wow, it really did say that... and I completely read right over it.

Thanks for pointing that out (I just corrected my code to account for it)..

Rick Rothstein (MVP - Excel)

You guys are awesome -- I was more than happy with the core logic you
both provided, and had been modifying it for some of those specifics
in my data (as needed), but thanks for sticking with it and hammering
it out further. Truly appreciated!