FIND SPECIFIC VALUE...IF FOUND RETURN SPECIFIC VALUE

[Nasturtium]

I have a column with 3500 addresses. I need to search the column for things like Unit?? or RR?? or PO Box??. The addresses are not all entered the same. Some have the Unit at the front and some at the end. Same with PO Box. If it searches using a wildcard like Unit???/Unit* and finds the value Unit 7 then return that value. If it finds Unit 12 then return Unit 12. I can create more than one search so it's not all in one formula. I can do all the Units first then RR then PO Box, etc. I'll want the results of the search posted in the adjacent column.

ConnectON V11.xlsm
	F
45	25 Raglan Street
46	9608 Carter Road
47	250 Elm St
48	5139 Trussler Road RR 3
49	395 Waydom Drive
50	2132 Greenfield Road
51	2132 Greenfield Road
52	2132 Greenfield Road
53	1271 Reidsville Road
54	141705 Grey Road 9
55	141705 Grey Road 9
56	Portage Landing 3119 Highway 169, Unit 2
57	1074 Cranberry Road , PO Box 24
58	1074 Cranberry Road , PO Box 24
59	26 Saunders Road
60	75 Welham Road
61	75 Welham Road
62	31 King Street, Unit 6
ConnectON

When I'm done I want all the addresses to just contain the street number and name. I want to remove all the references to units, RR, PO Box. I'll want to delete them after I've found them and placed them in the adjacent column. I want it to look something like the example below

ConnectON V11.xlsm
	A	B
1	25 Raglan Street
2	9608 Carter Road
3	250 Elm St
4	5139 Trussler Road	RR 3
5	395 Waydom Drive
6	2132 Greenfield Road
7	2132 Greenfield Road
8	2132 Greenfield Road
9	1271 Reidsville Road
10	141705 Grey Road 9
11	141705 Grey Road 9
12	Portage Landing 3119 Highway 169	Unit 2
13	1074 Cranberry Road	PO Box 24
14	1074 Cranberry Road	PO Box 24
15	26 Saunders Road
16	75 Welham Road
17	75 Welham Road
18	31 King Street	Unit 6
Sheet1

I hope all this makes sense. Please feel free to ask if you need additional information.

 

[alansidman]

I can give you a True or False in the next column with Power Query. This can then be filtered for all "Trues."

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "Unit") or Text.Contains([Column1],"PO Box") or Text.Contains([Column1],"RR ") then "True" else "False")
in
    #"Added Custom"

Book10
	A	B
1	Column1	Custom
2	25 Raglan Street	False
3	9608 Carter Road	False
4	250 Elm St	False
5	5139 Trussler Road RR 3	True
6	395 Waydom Drive	False
7	2132 Greenfield Road	False
8	2132 Greenfield Road	False
9	2132 Greenfield Road	False
10	1271 Reidsville Road	False
11	141705 Grey Road 9	False
12	141705 Grey Road 9	False
13	Portage Landing 3119 Highway 169, Unit 2	True
14	1074 Cranberry Road , PO Box 24	True
15	1074 Cranberry Road , PO Box 24	True
16	26 Saunders Road	False
17	75 Welham Road	False
18	75 Welham Road	False
19	31 King Street, Unit 6	True
Table1

 

[cmowla]

I was going to write VBA code to do this, but I used helper columns with Excel formulas instead.

Here are the formulas:

Instructions:

• In the top/header row, type in all of the address parts "PO Box", "RR " (with the space!), "Unit",etc.
• Put

  =IF(H45<>"",IF(J45>0,MID(F45,1,J45-1),MID(F45,1,I45-1)),F45)

  in cell G45 and fill down.
• Put

  =IF(I45>0,MID(F45,I45,LEN(F45)),"")

  in Cell H45 and fill down.
• Put

  =SUM(K45:M45)

  in Cell I45 and fill down. (This assumes that you won't have both Unit # and PO Box # in the same cell, for example.)
• Put

  =IFERROR(SEARCH(",",F45,1),0)

  in Cell J45 and fill down.
• Put

  =IFERROR(SEARCH(K$44,$F45,1),0)

  in Cell K45. Fill right to the last address part column and then fill down

You can simply hide these helper columns after you're done.

If you want someone to write VBA code to do this, let us know (since you have a .xlsm file extension).

 

[Nasturtium]

I was going to write VBA code to do this, but I used helper columns with Excel formulas instead.
View attachment 47495

Here are the formulas:
View attachment 47497

Instructions:

• In the top/header row, type in all of the address parts "PO Box", "RR " (with the space!), "Unit",etc.
• Put

  =IF(H45<>"",IF(J45>0,MID(F45,1,J45-1),MID(F45,1,I45-1)),F45)

  in cell G45 and fill down.
• Put

  =IF(I45>0,MID(F45,I45,LEN(F45)),"")

  in Cell H45 and fill down.
• Put

  =SUM(K45:M45)

  in Cell I45 and fill down. (This assumes that you won't have both Unit # and PO Box # in the same cell, for example.)
• Put

  =IFERROR(SEARCH(",",F45,1),0)

  in Cell J45 and fill down.
• Put

  =IFERROR(SEARCH(K$44,$F45,1),0)

  in Cell K45. Fill right to the last address part column and then fill down

You can simply hide these helper columns after you're done.

If you want someone to write VBA code to do this, let us know (since you have a .xlsm file extension).

That is amazing! This will do nicely! No need for VBA. There's always more than one way to solve it but if it works why do anything else!! Thanks again!

 

[cmowla]

That is amazing! This will do nicely! No need for VBA. There's always more than one way to solve it but if it works why do anything else!! Thanks again!

You're quite welcome! I'm glad that this worked for you!

(And obviously there is no limit to the number of address parts you can use. Just be sure to take note of the extra space in "RR ", because the formulas would mark cells that have rr (as in "Cranberry Road"). Yeah, it's not very smart!