Excel 2000: How to find a certain character in ANY Column?
Using Excel 2000, I posted here and John Bundy told us how to select
only the Rows that have a certain character (@), by using a "Helper" column with =IF(ISERROR(FIND("@",A1)),"","1") Cool. That worked magnificently. Now,... for another file, I need to do the same thing, but the email address might be in Any Column. I tried =IF(ISERROR(FIND("@",A1:Z1)),"","1") but that didn't work. I need to select only the Rows... that could have an email address in any Column. Can it be done? Thanks very much, in advance, people. Mark246 |
Excel 2000: How to find a certain character in ANY Column?
Try adding this function to the workbook code
Function FindPattern(myrange As Range, myPattern As String) Dim r As Range myPattern = "*" & myPattern & "*" FindPattern = "" For Each r In myrange If r.Text Like myPattern Then FindPattern = 1 Exit Function End If Next r End Function In the cell, enter =FINDPATTERN(A1:Z1,"@") "Mark246" wrote: Using Excel 2000, I posted here and John Bundy told us how to select only the Rows that have a certain character (@), by using a "Helper" column with =IF(ISERROR(FIND("@",A1)),"","1") Cool. That worked magnificently. Now,... for another file, I need to do the same thing, but the email address might be in Any Column. I tried =IF(ISERROR(FIND("@",A1:Z1)),"","1") but that didn't work. I need to select only the Rows... that could have an email address in any Column. Can it be done? Thanks very much, in advance, people. Mark246 |
Excel 2000: How to find a certain character in ANY Column?
Thanks, Barb.
Wow. This is new to me, but I'm trying... I've read the "Getting started with User Defined Functions", created the "personal.xls" file, entered that text (from "Function..." down to "End Function") starting in A1, saved that personal.xls file in the XLSTART directory, opened my datafile.xls, inserted a column 1 and put "=personal.xls!findpattern(B1:Z1,"@")" in A1, and it says #NAME?. Help, please? Thanks very much, in advance, people. Mark246 |
Excel 2000: How to find a certain character in ANY Column?
And you allow macros to run (tools|macro|security)?
And did you put findpattern in a general module--not behind a worksheet and not behind ThisWorkbook? Mark246 wrote: Thanks, Barb. Wow. This is new to me, but I'm trying... I've read the "Getting started with User Defined Functions", created the "personal.xls" file, entered that text (from "Function..." down to "End Function") starting in A1, saved that personal.xls file in the XLSTART directory, opened my datafile.xls, inserted a column 1 and put "=personal.xls!findpattern(B1:Z1,"@")" in A1, and it says #NAME?. Help, please? Thanks very much, in advance, people. Mark246 -- Dave Peterson |
Excel 2000: How to find a certain character in ANY Column?
Thanks very much for the reply, Dave.
Security WAS set at High; I set it to Medium. You asked: "And did you put findpattern in a general module--not behind a worksheet and not behind ThisWorkbook?" I dunno' what that means. Still,... When I went to "Tools/Macro/Security", I saw the "Macro/ RecordNewMacro". Maybe I didn't RECORD or SAVE the macro how it should have been saved. I tried the RecordNewMacro thing, and re-typed the entire thing, starting at a random cell... G8 IN the personal.xls file. Then saved it. Then exited Excel. Then restarted it. It still doesn't work. Any ideas, people? Thanks in advance. |
Excel 2000: How to find a certain character in ANY Column?
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. ===== And since I don't know where you put the other code, you may want to delete it. Mark246 wrote: Thanks very much for the reply, Dave. Security WAS set at High; I set it to Medium. You asked: "And did you put findpattern in a general module--not behind a worksheet and not behind ThisWorkbook?" I dunno' what that means. Still,... When I went to "Tools/Macro/Security", I saw the "Macro/ RecordNewMacro". Maybe I didn't RECORD or SAVE the macro how it should have been saved. I tried the RecordNewMacro thing, and re-typed the entire thing, starting at a random cell... G8 IN the personal.xls file. Then saved it. Then exited Excel. Then restarted it. It still doesn't work. Any ideas, people? Thanks in advance. -- Dave Peterson |
Excel 2000: How to find a certain character in ANY Column?
COOL ! It Works! Thanks a bunch, Dave.
I had already read David McRitchie's intro... still couldn't quite figure it out. Your abbreviated instructions were great. Thanks again. |
All times are GMT +1. The time now is 05:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com