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

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

Last Comment

ASKER

Can I do it as a function, without the vba?

Okay try this....

In E2

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"
```

ASKER

Fantastic!

Should also exclude any reference to a suite number (i.e. Ste 206).

Should also exclude any reference to a suite number (i.e. Ste 206).

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"
```

ASKER

```
=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"
```

I tried this syntax with E2 instead of D2 because I inserted column C for this output. It isn't working.

ASKER

It's working now. I must have done something weird, sorry for the confusion

Glad to know that. :)

ASKER

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:

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"
```

ASKER

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

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

Ok, this is good but I do not need the string " Branch" anymore, and I don't think that I need the " - " either.

Well in that case, try this.....

In C2

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),""))))
```

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

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:Open in new window

For details, refer to the attached where I have inserted the formula in col. E, highlighted yellow.