Append to B2 with exclusions

Append to contents of B2 with a dash (-), plus the contents from D2, and add the word "Branch" to the end.

Exclude integer at the beginning of an address.
Exclude every single character reference.
Example: If the company name in B2 is "ADVANCE STORES COMPANY, INCORPORATED" and the address in D2 is "610 N Loop 340", then the result would be "ADVANCE STORES COMPANY, INCORPORATED - Loop 340 Branch"
sample-for-ee.csv
frugalmuleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this....

1) Open your workbook

2) Press Alt+F11 to open VB Editor

3) On VB Editor --> Insert --> Module and paste the Function code given below into the opened code window.

4) Close VB Editor and save your workbook as Macro-Enabled Workbook

Now you can use the function CompanyWithAddress on the sheet as below...
In E2
=CompanyWithAddress(B2,D2)
and then copy it down.

Function Code:
Function CompanyWithAddress(cRng As Range, aRng As Range) As String
Dim RE As Object
Dim addStr As String
Set RE = CreateObject("VBScript.RegExp")
RE.Pattern = "\d+\s(\w\s)?"
If RE.Test(aRng.Value) Then
addStr = RE.Replace(aRng.Value, "")
End If
CompanyWithAddress = cRng & " - " & addStr & " Branch"
End Function

Open in new window


For details, refer to the attached where I have inserted the formula in col. E, highlighted yellow.
sample-for-ee.xlsm
frugalmuleAuthor Commented:
Can I do it as a function, without the vba?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay try this....

In E2

=B2&" - "&IF(FIND(" ",REPLACE(D2,1,FIND(" ",D2),""))=2,REPLACE(REPLACE(D2,1,FIND(" ",D2),""),1,2,""),REPLACE(D2,1,FIND(" ",D2),""))&" Branch"

Open in new window

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

frugalmuleAuthor Commented:
Fantastic!  

Should also exclude any reference to a suite number (i.e. Ste 206).
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this.....
=B2&" - "&IF(FIND(" ",REPLACE(D2,1,FIND(" ",D2),""))=2,IFERROR(REPLACE(REPLACE(REPLACE(D2,1,FIND(" ",D2),""),1,2,""),FIND("Ste ",REPLACE(REPLACE(D2,1,FIND(" ",D2),""),1,2,"")),255,""),REPLACE(REPLACE(D2,1,FIND(" ",D2),""),1,2,"")),IFERROR(REPLACE(REPLACE(D2,1,FIND(" ",D2),""),FIND("Ste ",REPLACE(D2,1,FIND(" ",D2),"")),255,""),REPLACE(D2,1,FIND(" ",D2),"")))&" Branch"

Open in new window

frugalmuleAuthor Commented:
=B2&" - "&IF(FIND(" ",REPLACE(E2,1,FIND(" ",E2),""))=2,IFERROR(REPLACE(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,""),FIND("Ste ",REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),255,""),REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),IFERROR(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),FIND("Ste ",REPLACE(E2,1,FIND(" ",E2),"")),255,""),REPLACE(E2,1,FIND(" ",E2),"")))&" Branch"

Open in new window


I tried this syntax with E2 instead of D2 because I inserted column C for this output.  It isn't working.
frugalmuleAuthor Commented:
It's working now.  I must have done something weird, sorry for the confusion
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad to know that. :)
frugalmuleAuthor Commented:
Ok, I do have small handful of #VALUE! errors.

One of them appears when the cell in the E column is blank, but another one appears when the value in the E cell is 708 Saratoga which makes little sense to me.

I'm using the following syntax:
=B2&" - "&IF(FIND(" ",REPLACE(E2,1,FIND(" ",E2),""))=2,IFERROR(REPLACE(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,""),FIND("Ste ",REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),255,""),REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),IFERROR(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),FIND("Ste ",REPLACE(E2,1,FIND(" ",E2),"")),255,""),REPLACE(E2,1,FIND(" ",E2),"")))&" Branch"

Open in new window

frugalmuleAuthor Commented:
Here is a subset of the sheet sorted by the 14 cells out of 1,088 total cells that are causing the #VALUE! error.
EE-value-error.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay try this...

In C2
=IF(E2="",B2,B2&" - "&IF(IFERROR(FIND(" ",REPLACE(E2,1,FIND(" ",E2),"")),0)=2,IFERROR(REPLACE(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,""),FIND("Ste ",REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),255,""),REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),IFERROR(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),FIND("Ste ",REPLACE(E2,1,FIND(" ",E2),"")),255,""),REPLACE(E2,1,FIND(" ",E2),"")))&" Branch")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frugalmuleAuthor Commented:
Ok, this is good but I do not need the string " Branch" anymore, and I don't think that I need the " - " either.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well in that case, try this.....

In C2
=IF(E2="",B2,B2&"  "&IF(IFERROR(FIND(" ",REPLACE(E2,1,FIND(" ",E2),"")),0)=2,IFERROR(REPLACE(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,""),FIND("Ste ",REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),255,""),REPLACE(REPLACE(E2,1,FIND(" ",E2),""),1,2,"")),IFERROR(REPLACE(REPLACE(E2,1,FIND(" ",E2),""),FIND("Ste ",REPLACE(E2,1,FIND(" ",E2),"")),255,""),REPLACE(E2,1,FIND(" ",E2),""))))

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.