Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Character in Text and put value in next column | Excel Worksheet Functions | |||
How to find data character width in Excel | Excel Discussion (Misc queries) | |||
find a character and replace with a command in Excel | Excel Discussion (Misc queries) | |||
Find a "date" in a column of dates in Excel 2000 | Excel Worksheet Functions | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) |