|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 1st, 2008, 02:29 AM
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
August 1st, 2008, 03:10 AM
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
August 1st, 2008, 03:28 AM
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
August 1st, 2008, 04:42 AM
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
August 1st, 2008, 06:14 AM
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
August 1st, 2008, 10:05 AM
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.
|
|
#7
August 1st, 2008, 04:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with Search/Find???
You could also use it to change text numeric values to real numbers.
With say, a 4 digit numerical list in Column A, and you're trying to find the 1st number in a datalist: =Vlookup(--Left(A1),C1:E100,3,0) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "TotallyConfused" wrote in message ... 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
