Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Paul" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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 "Paul" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. On Jul 24, 2:37 pm, Wigi wrote: 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. -- Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music "Paul" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Paul" wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
On Jul 27, 4:19 pm, "Ragdyer" wrote:
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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. Thank you! wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Paul" wrote in message ... On Jul 27, 4:19 pm, "Ragdyer" wrote: 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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. Thank you! wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
On Sun, 27 Jul 2008 12:50:46 -0700 (PDT), Paul wrote:
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. 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 he http://xcell05.free.fr/english/index.html --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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. On Jul 29, 11:24*pm, "RagDyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"Paul" wrote in message ... On Jul 27, 4:19 pm, "Ragdyer" wrote: 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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. *Thank you! wrote in message .... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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.- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
On Jul 29, 11:50*pm, Ron Rosenfeld wrote:
On Sun, 27 Jul 2008 12:50:46 -0700 (PDT), Paul wrote: 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. 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 hehttp://xcell05.free.fr/english/index.html Thank you, Ron. Unfortunately, I have to use functionality within Excel. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
On Thu, 31 Jul 2008 06:47:25 -0700 (PDT), Paul wrote:
On Jul 29, 11:50*pm, Ron Rosenfeld wrote: On Sun, 27 Jul 2008 12:50:46 -0700 (PDT), Paul wrote: 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. 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 hehttp://xcell05.free.fr/english/index.html Thank you, Ron. Unfortunately, I have to use functionality within Excel. 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
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 ! ----------------------------------------------------------------------------------------------- "Paul" wrote in message ... 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. On Jul 29, 11:24 pm, "RagDyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"Paul" wrote in message ... On Jul 27, 4:19 pm, "Ragdyer" wrote: 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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. Thank you! wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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.- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
Multiple strings.
On Jul 31, 11:08 am, "RagDyeR" wrote: 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 ! ----------------------------------------------------------------------------------------------- "Paul" wrote in message ... 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. On Jul 29, 11:24 pm, "RagDyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"Paul" wrote in message ... On Jul 27, 4:19 pm, "Ragdyer" wrote: 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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. Thank you! wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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.- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
On Jul 31, 10:39 am, Ron Rosenfeld wrote:
On Thu, 31 Jul 2008 06:47:25 -0700 (PDT), Paul wrote: On Jul 29, 11:50 pm, Ron Rosenfeld wrote: On Sun, 27 Jul 2008 12:50:46 -0700 (PDT), Paul wrote: 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. 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 hehttp://xcell05.free.fr/english/index.html Thank you, Ron. Unfortunately, I have to use functionality within Excel. 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 Understood. It really puts the pressure on carving out time to learn VBA. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Regular expression search/replace in Excel
Then you have to let us know what they are!
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Paul" wrote in message ... Multiple strings. On Jul 31, 11:08 am, "RagDyeR" wrote: 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 ! ----------------------------------------------------------------------------------------------- "Paul" wrote in message ... 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. On Jul 29, 11:24 pm, "RagDyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"Paul" wrote in message ... On Jul 27, 4:19 pm, "Ragdyer" wrote: 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 anywhe =A$1&" "&A2 And copy down as needed. -- HTH, RD RD, that does indeed help, and fits the bill of my currently limited Microsoft experience. Thank you! wrote in message ... 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. On Jul 24, 2:47 pm, TomPl wrote: 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 "Paul" wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and replace help in Excel | Excel Discussion (Misc queries) | |||
Excel formula needed... regular expression? | Excel Discussion (Misc queries) | |||
Regular Expression | Excel Discussion (Misc queries) | |||
How do I search and replace text in a textbox in Excel? | Excel Discussion (Misc queries) | |||
How do I do a search and replace in a textbox in Excel? | Excel Discussion (Misc queries) |