IF(FIND.. need help!

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
164
Office Version
  1. 365
Platform
  1. 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:


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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
164
Office Version
  1. 365
Platform
  1. Windows
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
811
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,954
Members
431,689
Latest member
jacker01

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