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]

No it's not.

+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	43
4	I 83 0000-282-627 KPI NATIONAL 1.0 $0.00	83
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	59
9	R 60 0000-524-891 FASCIA 6 S 1.0 $0.00	60
10	R 61 0000-677-440 F5.5S CM WH 1.0 $0.00	61
11	R 62 0000-386-653 QUICKSTRT RL 1.0 $0.00	62
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("000",A2)))-FIND(" ",A2)))

 

[rex759]

OOPS! Sorry about that. Its not the same

 

[rex759]

It worked except for row 7, it returns H2732-610352222. Also, the "000", there are times where when it starts with a 1000. It should be 58

 

[Fluff]

I'm afraid you've lost me. Could you post some more sample data & the expected outcome.

 

[rex759]

The line number always follows an R and the space before 0000 or 1000. Hope this makes sense

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

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

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1	Inventory	Order #	Line #
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	58
8	R 59 0000-678-905 SOFFIT V W 1.0 $0.00		59
9	R 60 0000-524-891 FASCIA 6 S 1.0 $0.00		60
10	R 61 0000-677-440 F5.5S CM WH 1.0 $0.00		61
11	R 62 0000-386-653 QUICKSTRT RL 1.0 $0.00		62
12	02/18/2020 W849994803 House Henkel R 1001 1001-382-439	W849994803	1001
13	R 62 1000-386-653 QUICKSTRT RL 1.0 $0.00		62
Data

Cell Formulas
Range	Formula
B2:B13	B2	=TRIM(MID(A2,FIND(" ",A2),IFERROR(SEARCH("cat",A2),IFERROR(SEARCH("house",A2),FIND(" ",A2)))-FIND(" ",A2)))
C2:C13	C2	=IFERROR(MID(A2,FIND(" R "," "&A2)+2,FIND(" ",A2,FIND(" R "," "&A2)+2)-FIND(" R "," " &A2)-2),"")

 

[rex759]

You rule! Thank you once again.

 

[Fluff]

My pleasure & thanks for the feedback.

 

[rex759]

Hello,
I wasn’t sure if I needed to start a new post but I have a follow-up question. Earlier in this post, you helped me achieve the results I needed with this formula;

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

I made a slight update as the format of the report changed;

=TRIM(MID(A2,FIND(" ",A2),IFERROR(SEARCH("cat",A2),FIND(" ",A2))-FIND(" ",A2)))

This has been working great except, sometimes; there isn’t a space to Find because the string appears in the beginning of the cell as in example, A9.
Is there a way to alter the formula or do I need to add a helper column?

Your help is appreciated.

Book1
	A	B
1	Inventory	Order #
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	W848156480 cat Henkel R 1001 1001-382-439
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 cat Henkel R 1001 1001-382-439	W849994803
13	R 62 1000-386-653 QUICKSTRT RL 1.0 $0.00
Sheet5

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

 

[Fluff]

How about
=TRIM(RIGHT(SUBSTITUTE(REPLACE(A2,IFERROR(SEARCH("cat",A2)-1,1),100,"")," ",REPT(" ",100)),100))