We help IT Professionals succeed at work.

Append to B2 with exclusions

Ted Penner
Ted Penner asked
on
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
Comment
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Ted PennerData Structures Engineer

Author

Commented:
Can I do it as a function, without the vba?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Ted PennerData Structures Engineer

Author

Commented:
Fantastic!  

Should also exclude any reference to a suite number (i.e. Ste 206).
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Ted PennerData Structures Engineer

Author

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.
Ted PennerData Structures Engineer

Author

Commented:
It's working now.  I must have done something weird, sorry for the confusion
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad to know that. :)
Ted PennerData Structures Engineer

Author

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

Ted PennerData Structures Engineer

Author

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
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Ted PennerData Structures Engineer

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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