Regular expression search/replace in Excel

P

Paul

Without delving into VBA (I have limited experience, and now is not
the time to learn), and without installing augmentation software, is
it possible to search for all text matching a search pattern, then
prepend each occurance with given sequence of characters?

If not, is it possible to simply prepend a given sequence of
characters the string in each cell of a selected region?

Thanks.
 
W

Wigi

You could make a loop through these cells, and use Like to test whether the
cells match some kind of pattern. See in the helpfiles for the wildcards you
can use.
 
T

TomPl

This formula might work in your situation. B5 being the cell to analyse,
"There" being the text you are looking for and "Hello" as the text to prepend.

Find is case sensative.

=IF(ISERR(FIND("There",B5)),B5,"Hello " & B5)

Tom
 
P

Paul

Wigi,

I appreciate the tip. I was hoping that there was a way to do it
without delving in to VBA (and I wasn't clear about that in my OP, now
that I review it). From the responses so far, I assume that it is not
possible.
 
P

Paul

Tom,

Thanks for the code snippet. I wasn't clear in my OP, but I was
seeking a non-VBA method of search-and-replace with regular
expressions. But from the lack of responses in that vein, I'm
assuming that there isn't such a solution.
 
R

Ragdyer

Is this something you're looking for:

Text in A2 to An.

To insert the text:
P.O. #
at the beginning of each cell.

In A1 enter:
P.O. #

Enter this formula anywhere:

=A$1&" "&A2
And copy down as needed.
 
P

Paul

Is this something you're looking for:

Text in A2 to An.

To insert the text:
P.O. #
at the beginning of each cell.

In A1 enter:
P.O. #

Enter this formula anywhere:

=A$1&" "&A2
And copy down as needed.
Click to expand...

RD, that does indeed help, and fits the bill of my currently limited
Microsoft experience. Thank you!
 
R

RagDyer

In re-reading your OP, are you looking to ... say ...
add that "P.O. #" text to *only certain* cells that might contain a specific
text string?

If you're still looking for help in that vein, post back with some
specifics.
 
R

Ron Rosenfeld

Tom,

Thanks for the code snippet. I wasn't clear in my OP, but I was
seeking a non-VBA method of search-and-replace with regular
expressions. But from the lack of responses in that vein, I'm
assuming that there isn't such a solution.
Click to expand...

Microsoft Excel does not support regular expressions.

VBA supports the Javascript flavor.

Longre has written a free add-in: morefunc.xll which as regular expression
functions which support the PCRE flavor. Apparently because it is an .xll, it
will not work with strings > 255 characters, though. You can find it here:
http://xcell05.free.fr/english/index.html

--ron
 
P

Paul

Yes, that is what I meant in my first paragraph. RD's solution help
if I want to manually go through and find these cells myself.
 
P

Paul

Microsoft Excel does not support regular expressions.

VBA supports the Javascript flavor.

Longre has written a free add-in:  morefunc.xll which as regular expression
functions which support the PCRE flavor.  Apparently because it is an .xll, it
will not work with strings > 255 characters, though.  You can find it here:http://xcell05.free.fr/english/index.html
Click to expand...

Thank you, Ron. Unfortunately, I have to use functionality within
Excel.
 
R

Ron Rosenfeld

Thank you, Ron. Unfortunately, I have to use functionality within
Excel.
Click to expand...

Well, if you can't use VBA, and can't use add-ins, the only other tool of which
I am aware would be the Find/Replace within Excel. That tool does not support
regular expressions.
--ron
 
R

RagDyeR

Exactly *what* cells are you looking for?

Is it one specific text string or number, or multiple ones?
 
P

Paul

Multiple strings.

Exactly *what* cells are you looking for?

Is it one specific text string or number, or multiple ones?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Yes, that is what I meant in my first paragraph. RD's solution help
if I want to manually go through and find these cells myself.
Click to expand...
 
P

Paul

Well, if you can't use VBA, and can't use add-ins, the only other tool of which
I am aware would be the Find/Replace within Excel. That tool does not support
regular expressions.
--ron
Click to expand...

Understood. It really puts the pressure on carving out time to learn
VBA.
 
Top