Multiple formulas in one cell

[spencerb]

I have multiple formulas that I need to run in one cell. I need to run them in the following order -

In cell C2 -
The 1st formula is: =SUBSTITUTE(H2,", United States","")
The 2nd formula is: =LEFT(H2,FIND(CHAR(1),SUBSTITUTE(H2,",",CHAR(1),LEN(H2)-LEN(SUBSTITUTE(H2,",",""))))-1)

I can't seem to find a way to run both formulas in the same cell. They have to be run in that order or the result will not be correct sometimes. I am trying to remove ", United States" so the second formula will work correctly and remove the city, state, and zip.

The final result should look like this:

I can accomplish this with the two formulas in seperate columns, but it would be much cleaner if it was all in one column.

Thank you,
Spencer

 

[Larry Haydn]

Sometimes, instead of trying to do everything in a single formula, just split them into 2 or more formulas in different cells.
In the example below, I place the first formula in Col I, then in Col C, I reference Col I in the second formula.

 

[Sahak]

Hi Spencerb

If all full Mailing addresses will be entered in the same way:

• Street address (No Commas)
• , (comma)
• City, State, Zip (no meter if there are commas or country)

For example 2000 16th Street Suite 200, Washington, DC 20005, United State

Then you can use:

=LEFT(H2,FIND(",",H2,1)-1)

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Company	Street Address	City	St	Zip	-	Full Mailing Address
2	Bob Smith	Smith Company	123 South Main Street	New York	NY	10018		123 South Main Street, New York 10018, United States
3	Bill Jones	Jones Company	456 North Main Street	New York	Ny	10019		456 North Main Street, New York NY 10019
4	Sally Johnson	Johnson Company	2000 16th Street, Suite 200	Washington	DC	20005		2000 16th Street, Suite 200, Washington, DC 20005, United States
5
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=MID(H2,1,FIND(D2,H2)-3)

 

[spencerb]

First off thank you to the three people who sent great solutions to the problem. Unfortunately, none will work in my situation.

Also, I had a miss type in my original post. I put in a comma between Washington and DC. In my database, there is never a comma between the City and ST. It should have looked like this...

@Larry Haydn - Thank you for your solution. I may have to fall back on this. As stated, two formulas in separate columns work, but it would be much cleaner if it was all in one column. I need to have the user copy the formulas down the column and if they only had one to copy it would be simpler.

@Sahak - Thank you for your solution, but the suite numbers sometimes have a comma and I need them to stay if there is one. Your formula strips out everything after the first comma. Therefore, your formula in the example above, Sally Johnson's address would show as 2000 16th Street and not show Suite 200.

@Sufiyan97 - Thank you for your solution, I like this solution the best because it is simple and looks specifically for the city, but sometimes there is a street name that matches the city name. Therefore, using your formula, a full address that looked like this:

123 Tysons Blvd, Suite 200, Tysons VA 22182
would show as:
12

Is there a way to ignore the first instance of the city name if there are two?

Again, thank you to all who read my post and especially those who provided viable solutions. Maybe I will have to go back to the two-column approach.

Spencer

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=LEFT(H2,SEARCH(D2&" "&E2,H2)-3)

 

[spencerb]

@Fluff - Yes! It's simple and it works!

It pointed out some errors in my database, but it works.

Thank you!
Spencer

 

[spencerb]

Oh, and I did update my Account Info too.

I'm using Microsoft 365 for this project, but we also use v2019.

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.
Thanks for updating your profile as well

 

[Sufiyan97]

First off thank you to the three people who sent great solutions to the problem. Unfortunately, none will work in my situation.

Also, I had a miss type in my original post. I put in a comma between Washington and DC. In my database, there is never a comma between the City and ST. It should have looked like this...

View attachment 54146



@Sufiyan97 - Thank you for your solution, I like this solution the best because it is simple and looks specifically for the city, but sometimes there is a street name that matches the city name. Therefore, using your formula, a full address that looked like this:

123 Tysons Blvd, Suite 200, Tysons VA 22182
would show as:
12

Is there a way to ignore the first instance of the city name if there are two?

Try

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Company	Street Address	City	St	Zip	-	Full Mailing Address
2	Bob Smith	Smith Company	123 South Main Street	New York	NY	10018		123 South Main Street, New York 10018, United States
3	Bill Jones	Jones Company	456 North Main Street	New York	Ny	10019		456 North Main Street, New York NY 10019
4	Sally Johnson	Johnson Company	2000 16th Street, Suite 200	Washington	DC	20005		2000 16th Street, Suite 200, Washington, DC 20005, United States
5			123 Tysons Blvd, Suite 200	Tysons	VA	22182		123 Tysons Blvd, Suite 200, Tysons VA 22182
6
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(LEFT(H2,SEARCH(D2,SUBSTITUTE(H2,D2,"@",1))+LEN(D2)-4),LEFT(H2,SEARCH(D2,H2)-3))