Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
=B2&" - "&IF(FIND(" ",REPLACE(D2,1,FIND(" ",D2),""))=2,REPLACE(REPLACE(D2,1,FIND(" ",D2),""),1,2,""),REPLACE(D2,1,FIND(" ",D2),""))&" Branch"
=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"
=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"
=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"
=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")
=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),""))))
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.