Excel - Separation of Address Requirement

Vinod GV

Banned - Rules violations
Joined
Oct 19, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
Hi
In Column A, I get a file where I have address, city , state and Zip code in one single cell. I need to split them into separate
I need to manual separate them, rather looking for a code or formula which can separate them
I have shared the screenshot of the outcome required, can you please assist me on this.
 

Attachments

  • Excel file - JPEG.jpg
    Excel file - JPEG.jpg
    132.8 KB · Views: 7

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
You could get the state and zip but the rest isnt possible unless you maybe have a list of cities that it could be compared against. A machine cant know what a street or a city is and there is nothing there to separate them.
 

Vinod GV

Banned - Rules violations
Joined
Oct 19, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
This is floating, we are not sure on the ask of the address.
Can you please share the formula for atleast for Zip and state
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
As the zip and state are always of the same length and always at the end of the address you can use mid and right formulas such as:

=MID(A1,LEN(A1)-6,2)
=0+RIGHT(A1,5)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,807
Office Version
  1. 365
Platform
  1. Windows
Cross posted
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Despite having being told before you are still ignoring the rules.
Please supply the link to the other site you have asked this question.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,435
Members
412,529
Latest member
cTatch
Top