Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
I have tried several ways to identify a rows in a column with the first three
characters are the same "BSB". The data reads in the column like: ColA row 5 - BSB12345 row 6 - John Doe row 7 - date row 8 - blank row 9 - blank row 10 - BSB78945 row 11 - Jane Doe row 12 - date row 13 - blank row 14 - blank so forth - I need to identify only the rows that start with BSB with a "Yes", "No" . I have tried: =SEARCH("BSB*", A5,1) (this does not fill the other rows). =IF(ISTEXT(SEARCH("BSB*",A5)),1,0) (this did not work) =IF(ISTEXT(A5)="BSB*",1,0) (this did not work either) Thank you for any help you can provide. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
Try in say, B5: = --(LEFT(A5,3)="BSB")
Copy down -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "TotallyConfused" wrote: I have tried several ways to identify a rows in a column with the first three characters are the same "BSB". The data reads in the column like: ColA row 5 - BSB12345 row 6 - John Doe row 7 - date row 8 - blank row 9 - blank row 10 - BSB78945 row 11 - Jane Doe row 12 - date row 13 - blank row 14 - blank so forth - I need to identify only the rows that start with BSB with a "Yes", "No" . I have tried: =SEARCH("BSB*", A5,1) (this does not fill the other rows). =IF(ISTEXT(SEARCH("BSB*",A5)),1,0) (this did not work) =IF(ISTEXT(A5)="BSB*",1,0) (this did not work either) Thank you for any help you can provide. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
This worked!! Thank you so much. Can you please explain? I don't
understand the "--". First time I see this used. Thank you. "Max" wrote: Try in say, B5: = --(LEFT(A5,3)="BSB") Copy down -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "TotallyConfused" wrote: I have tried several ways to identify a rows in a column with the first three characters are the same "BSB". The data reads in the column like: ColA row 5 - BSB12345 row 6 - John Doe row 7 - date row 8 - blank row 9 - blank row 10 - BSB78945 row 11 - Jane Doe row 12 - date row 13 - blank row 14 - blank so forth - I need to identify only the rows that start with BSB with a "Yes", "No" . I have tried: =SEARCH("BSB*", A5,1) (this does not fill the other rows). =IF(ISTEXT(SEARCH("BSB*",A5)),1,0) (this did not work) =IF(ISTEXT(A5)="BSB*",1,0) (this did not work either) Thank you for any help you can provide. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
Welcome, but pl take a moment to press the "Yes" button below
The "--" will coerce the TRUE/FALSE returns from the check: LEFT(A5,3)="BSB" to 1's/0's -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "TotallyConfused" wrote: This worked!! Thank you so much. Can you please explain? I don't understand the "--". First time I see this used. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
I pressed the "Yes" button. Is there any other uses for "--"? Are there any other situations where I could use? Thank you very much. "Max" wrote: Welcome, but pl take a moment to press the "Yes" button below The "--" will coerce the TRUE/FALSE returns from the check: LEFT(A5,3)="BSB" to 1's/0's -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "TotallyConfused" wrote: This worked!! Thank you so much. Can you please explain? I don't understand the "--". First time I see this used. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with Search/Find???
That's the main use of it.
-- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "TotallyConfused" wrote: I pressed the "Yes" button. Is there any other uses for "--"? Are there any other situations where I could use? Thank you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't find certain date in search | Excel Worksheet Functions | |||
search or find, and extract? | Excel Discussion (Misc queries) | |||
Find & Search Function | Excel Worksheet Functions | |||
Search and Find Macro | Excel Discussion (Misc queries) | |||
Can Search find 2 or more "/"? | Excel Discussion (Misc queries) |