IF(FIND.. need help!

[melodramatic]

OK, I'm trying to do a separation of data lines in order to set this data into separated columns, and am having problems with part of it.

The data giving me issues is this:

241216-RFQ-01

241216-RFQ-02(REV0)

241217-RFQ-01-rev 0

232221-PUR-05: Rev 0 - SYSTEM UPGRADE

241146-PUR-01-REV1 ANNOUNCEMENTS TEST

216119-CON-01: MAINTENANCE ANNUAL

241216-RFQ-02 (REV 1 UPDATED)

I'm simply breaking the data lines down into separated columns: Project Number (chars 1-6), Document Type (chars 8-10), Document # (chars 12-13).

The blip comes with the document Revision #. As you can tell, that's all over the board in this typed list. And, being that the list is close to 1000 lines long, it's a lttle hard to handle manually.

I've used a find/replace to turn all forms of Rev/rev into "REV", and then changed all REV(space) into REV(no space), so that the FIND calculation would work. And I got it working using...

=MID(B3,FIND("REV",B3,1)+3,1)

The only problem I have is for those lines that have absolutely no mention of a revision #. By default, that means 0, but I can't find how it can give me that automatically.

I've unsuccessfully tried the following:

IF(FIND("REV",B2,1)>0,MID(B3,FIND("REV",B3,1)+3,1),0)

but that still gives me the #VALUE! response.

How can I put in a code that will bypass the not found error, and put in a zero?

 

[njimack]

If(isnumber(find("rev",b2))...

 

[melodramatic]

Thanks! Someone else also sent me a message and told me to use IFERROR (d'uh - I totally blew that one).

=IFERROR(MID(B2,FIND("REV",B2,1)+3,1),0)

BOth of them work - thanks so much!

 

[Chrisdontm]

OK, I'm trying to do a separation of data lines in order to set this data into separated columns, and am having problems with part of it.
-----------
I've used a find/replace to turn all forms of Rev/rev into "REV", and then changed all REV(space) into REV(no space), so that the FIND calculation would work. And I got it working using...

=MID(B3,FIND("REV",B3,1)+3,1)
----------
I've unsuccessfully tried the following:

IF(FIND("REV",B2,1)>0,MID(B3,FIND("REV",B3,1)+3,1),0)
---------
How can I put in a code that will bypass the not found error, and put in a zero?

Melo, The first part that you say you have working is returning the first blank space after "REV", I am curious if that is what you are wanting as a return...???

Anyway, try this for the other part, see if it will suffice;

IF(IFERROR(FIND("REV",B2,1)>0,FALSE),MID(B2,FIND("REV",B2,1)+3,1),0)

Have fun... ☺

 

[AlKey]

OK, I'm trying to do a separation of data lines in order to set this data into separated columns, and am having problems with part of it.

The data giving me issues is this:

241216-RFQ-01

241216-RFQ-02(REV0)

241217-RFQ-01-rev 0

232221-PUR-05: Rev 0 - SYSTEM UPGRADE

241146-PUR-01-REV1 ANNOUNCEMENTS TEST

216119-CON-01: MAINTENANCE ANNUAL

241216-RFQ-02 (REV 1 UPDATED)

I've used a find/replace to turn all forms of Rev/rev into "REV", and then changed all REV(space) into REV(no space), so that the FIND calculation would work. And I got it working using...

There is no need to to use find/replace tool. You should've used SEARCH function instead of FIND. The FIND function is case-sensitive and SEARCH is not. The syntax for both function is the same.