melodramatic
Board Regular
- Joined
- Apr 28, 2003
- Messages
- 180
- Office Version
- 365
- Platform
- Windows
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:
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?
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?