IF(FIND.. need help!

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
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?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
149
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

Well-known Member
Joined
May 4, 2015
Messages
810
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

Active Member
Joined
Oct 15, 2013
Messages
395
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top