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.

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.

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.

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.

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.

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.