Combining formula on three conditions

[rex759]

Hello,

I am having trouble combining these two formulas into one. I thought I could use something like this:

=IF(ISERROR(REPLACE(LEFT(A85,FIND(" ",A85,FIND("CAT",A85)+1)-4),1,FIND(" ",A85),"")),REPLACE(LEFT(A85,FIND(" ",A85,FIND("HOUSE",A85)+1)-6),1,FIND(" ",A85),""),REPLACE(LEFT(A85,FIND(" ",A85,FIND("CAT",A85)+1)-4),1,FIND(" ",A85),""))

But when I do, it only works correctly when the string “CAT” is found. There are three options:
If A85 has Cat, House, or neither.

Any help is appreciated

 

[Fluff]

Can you please post some sample data, along with expected results using the XL2BB add-in?

 

[rex759]

Thank you for replying. I deleted a bunch of lines on the spreadsheet to make it easier to read so the ranges are not the same in my original post.

Book1
	A	B
1	Inventory
2	08/24/2019 H2732-59057 cat ODELL	H2732-59057
3	I 43 0000-484-403 VIATERA 3CM 1.0 $0.00
4	I 83 0000-282-627 KPI NATIONAL 1.0 $0.00
5	11/01/2019 H2732-59057 cat ODELL I 124 0000-282-627 KPI NATIONAL 1.0 $0.00	H2732-59057
6	07/17/2019 H2732-59477 cat KEENOY S 0710 0000-867-770 SBSD137HWW 1.0 $0.00	H2732-59477
7	10/28/2019 H2732-610352222 cat HAMMER R 58 0000-787-792 1/2 OSB 4.0 $0.00	H2732-610352222
8	R 59 0000-678-905 SOFFIT V W 1.0 $0.00
9	R 60 0000-524-891 FASCIA 6 S 1.0 $0.00
10	R 61 0000-677-440 F5.5S CM WH 1.0 $0.00
11	R 62 0000-386-653 QUICKSTRT RL 1.0 $0.00
12	02/18/2020 W849994803 house Henkel R 1001 1001-382-439	W849994803
13
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(REPLACE(LEFT(A2,FIND(" ",A2,FIND("cat",A2)+1)-4),1,FIND(" ",A2),""),"")
B12	B12	=REPLACE(LEFT(A12,FIND(" ",A12,FIND("house",A12)+1)-6),1,FIND(" ",A12),"")

 

[Fluff]

Thanks for that, how about

+Fluff New.xlsm
	A	B
1	Inventory
2	08/24/2019 H2732-59057 cat ODELL	H2732-59057
3	I 43 0000-484-403 VIATERA 3CM 1.0 $0.00
4	I 83 0000-282-627 KPI NATIONAL 1.0 $0.00
5	11/01/2019 H2732-59057 cat ODELL I 124 0000-282-627 KPI NATIONAL 1.0 $0.00	H2732-59057
6	07/17/2019 H2732-59477 cat KEENOY S 0710 0000-867-770 SBSD137HWW 1.0 $0.00	H2732-59477
7	10/28/2019 H2732-610352222 cat HAMMER R 58 0000-787-792 1/2 OSB 4.0 $0.00	H2732-610352222
8	R 59 0000-678-905 SOFFIT V W 1.0 $0.00
9	R 60 0000-524-891 FASCIA 6 S 1.0 $0.00
10	R 61 0000-677-440 F5.5S CM WH 1.0 $0.00
11	R 62 0000-386-653 QUICKSTRT RL 1.0 $0.00
12	02/18/2020 W849994803 house Henkel R 1001 1001-382-439	W849994803
Data

Cell Formulas
Range	Formula
B2:B12	B2	=TRIM(MID(A2,FIND(" ",A2),IFERROR(SEARCH("cat",A2),IFERROR(SEARCH("house",A2),FIND(" ",A2)))-FIND(" ",A2)))

 

[rex759]

Stepped out for a bit but will check when I get back. Looks much cleaner then mine!

 

[rex759]

Yes, it works perfectly. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rex759]

Can I ask one more question? I want to extract the number that falls between an R and the space before the 0000s. For example, in row 8, R 59 000-678-905 SOFFIT V W 1.0 $0.00, I want to extract the 59. In row 9, extract the 60 and so on. I was trying to incorporate Search and Find but cannot do it in one formula.

 

[Fluff]

How about
=TRIM(MID(A2,FIND(" ",A2),IFERROR(SEARCH("cat",A2),IFERROR(SEARCH("house",A2),FIND("000",A2)))-FIND(" ",A2)))

 

[rex759]

This is the same as the previous formula.