Free Microsoft Excel 2013 Quick Reference

Extract city, state and zip code from a single cell

is there a way to Extract city, state and zip code from a single cell

sample data:

4076 St. Andrews Ct. Canfield, OH 44406


Post your answer or comment

comments powered by Disqus
Does anyone have a formula to automatically extract the City, State,
and zip code from the following text string.

4076 St. Andrews Ct. Canfield, OH 44406

I am working in Excel, and have City, State, and Zip code in one column. I wish to break down the column into 3 separate columns: Col. 1: City, Col. 2: State, and Col. 3: Zip.
I know how to do that, and I'm using the LEN formula to extract what I need from the first column. However, I have ran into a problem. In the zip code column, I have some 5 digit zip codes while others are 9 digits. Once I use the len formula, it extracts the state as well as the zip code for all the 5 digit zips. Is there something else I can do to only extract the zip codes whether they are 9 or 5 digits? Please help! Thanks!

I have a text file that is 695 chacters long. In the line is a block of 50 chacters that all include the city state and zip code
I can pull this out by using =Mid(A1,442,50)
Now I have a string of 50 characters that have the City, State and Zip code.

The cities can be varrying lengths with a either no spaces or multiple spaces. Followed by a Space then the 2 digit State abbreviation, and a space and the zip code 5 or 9 digits The rest is spaces.

Does anyone know a way to start by looking at the right side of the cell and look at every chacter. If it is a space to delete it.
Once it finds a number (the last digit of the zip code) then =if(right(A2,6)=" ",Right(A2,5,5),right(A2,9,9) {to pull out the zip code}.

Then to get the State =if(Right(A2,6)=" ",Right(A2,8,2),Right(A2,12,2)

My problem using the mid formula is that I can't pinpoint the last digit of the zip code.

I have search the forums but kept coming accross examples where they are looking for a specfic value and then removing everything in front of that value.

HTML Code:
City State Zip                                                     City    State    Zip
Birmingham Al 352290001                                       
Long Beach Ca 908400000                                       
Washington Dc 200592005                                       
Holland Mi 49422                                              
For some reason I couldn't get the HTLM editor to show the spreadsheet

I have been given a spreadsheet with city, state and zip all in the same
cell. Is there a way to split the city, state and zip into three seperate
cells? I have a large data base and do not want to do each one at a time.

I got this list from my boss with instructions to separate the city, state and zip from the street address.
Unfortunately, The person who entered the data originally did a poor job, so there are often two city, state and zips or there is extra information after the zip or there is a dba included before the street address(which we don't need).

I already tried a formula to separate when 5 spaces in a row is discovered, after I formatted using the replace tool since most of the street names and city, state and zip were separated by 2 or more spaces. This turned out inconsistent, forcing me to check through the entire list fixing mistakes anyways.

I've attached examples of the original file and an example of after I used my formula.

Again, what I need is company name in one column, street address in another, and finally the city, state zip in a final column.

Here is the code I used


=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",
CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)


Hope you can help!

Thanks in advanced,

Sean W.

Hello,

This is a common problem I believe many people face is to automate a process that split name, address, city, state, and zip.

here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN.

911EP AQUISITION CORP ---------------row 1
13386 INTERNATIONAL PKWY------------row2
JACKSONVILLE, FL 32218---------------row3

911EP AQUISITION CORP ---------name
13386 INTERNATIONAL PKWY----------address
JACKSONVILLE--------------------city
FL -------------------------------state
32218---------------------------zip

A & P HARDWARE & BUILDING-----------row1
SUPPLY COMPANY----------------------row2
2757 OZARK AVE-----------------------row3
GASTONIA, NC 28052------------------row4

A & P HARDWARE & BUILDING SUPPLY COMPANY----------name
2757 OZARK AVE----------------------------------------address
GASTONIA---------------------------------------city
NC ------------------------------------------------state
28052--------------------------------------------zip

A&R METAL INDUSTRIES LTD---------------row1
2020 #6 RD-------------------------------row2
RICHMOND, BRITISH COLUMBIA V6V--------row3
1P1--------------------------------------row4
CANADA----------------------------------row5

A&R METAL INDUSTRIES LTD-----------------name
2020 #6 RD------------------------------address
RICHMOND ----------------------------------city
BRITISH COLUMBIA --------------------------state
V6V 1P1--------------------------------------zip
CANADA--------------------------------country

ZUKEN USA INC 238 LITTLETON RD STE 100 WESTFORD, MA 10886 ----------all in one row

ZUKEN USA INC------------------------------name
238 LITTLETON RD STE 100 -----------------address
WESTFORD--------------------------------city
MA --------------------------------state
10886-------------------------------zip

can any process in excel be automated even if the datas are not consistent and you don't know how you gonna get them from time to time?

if someone know how to automate this task or know of a software then let me know. I need this to be done with 100% accuracy with a given of about 30000 datas, is this possible?

NOTE: or very close to the 100%

thanks

Hello,

This is a common problem I believe many people face is to automate a process that split name, address, city, state, and zip.

here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN.

911EP AQUISITION CORP ---------------row 1
13386 INTERNATIONAL PKWY------------row2
JACKSONVILLE, FL 32218---------------row3

911EP AQUISITION CORP ---------name
13386 INTERNATIONAL PKWY----------address
JACKSONVILLE--------------------city
FL -------------------------------state
32218---------------------------zip

A & P HARDWARE & BUILDING-----------row1
SUPPLY COMPANY----------------------row2
2757 OZARK AVE-----------------------row3
GASTONIA, NC 28052------------------row4

A & P HARDWARE & BUILDING SUPPLY COMPANY----------name
2757 OZARK AVE----------------------------------------address
GASTONIA---------------------------------------city
NC ------------------------------------------------state
28052--------------------------------------------zip

A&R METAL INDUSTRIES LTD---------------row1
2020 #6 RD-------------------------------row2
RICHMOND, BRITISH COLUMBIA V6V--------row3
1P1--------------------------------------row4
CANADA----------------------------------row5

A&R METAL INDUSTRIES LTD-----------------name
2020 #6 RD------------------------------address
RICHMOND ----------------------------------city
BRITISH COLUMBIA --------------------------state
V6V 1P1--------------------------------------zip
CANADA--------------------------------country

ZUKEN USA INC 238 LITTLETON RD STE 100 WESTFORD, MA 10886 ----------all in one row

ZUKEN USA INC------------------------------name
238 LITTLETON RD STE 100 -----------------address
WESTFORD--------------------------------city
MA --------------------------------state
10886-------------------------------zip can any process in excel be automated even if the datas are not consistent and you don't know how you gonna get them from time to time?

if someone know how to automate this task or know of a software then let me know. I need this to be done with 100% accuracy with a given of about 30000 datas, is this possible?

NOTE: or very close to the 100%

http://www.mrexcel.com/forum/showthread.php?t=313985

thanks

I have a column that contains the city, state and zip all in one - please see examples below.
How can I separate the 3 fields and place the city in it's own field, the state in it's own field and the zip in it's own field

Billings,MT 59101
Laurel,MT 59044-9719
ST PAUL,MN 55113
LAUREL,MT 59044
BOYD,MT 59070-9998
COLFAX,WI 54730-4607
Grand Island,NE 68803-3107

Please remember, I know just enough to be dangerous, and in no means an expect. Thanks in advance for your time and help, I appreciate it!

I'm trying to concatenate address components (Address Line 1, Address Line 2,
City, State, and Zip Code) from their individual cells into a single cell
that is formatted correctly in 2 or 3 lines of text. In my formula, I'm
trying to find a way to output Address Line 1, start a new line, put Address
Line 2 on the new line, start a new line, and put the City, State, and Zip on
the last line.

I'm trying to concatenate address components (Address Line 1, Address Line 2,
City, State, and Zip Code) from their individual cells into a single cell
that is formatted correctly in 2 or 3 lines of text. In my formula, I'm
trying to find a way to output Address Line 1, start a new line, put Address
Line 2 on the new line, start a new line, and put the City, State, and Zip on
the last line.

All,

First, I have read through the board, and I was not able to find a solution already posted. I apologize if in fact this is a duplicate.

Second, I am comfortable with dynamic lists, dynamic ranges, chained validation lists, etc: so I am pretty sure my question is not a repeat of the other recent validation list questions.

Therefore:

Is it possible to generate a validation list from a single cell with a comma separated list?

For example, if I have “Fresh, Cool, Hot, Neutral, Smooth” in given cell, can I then have those 5 items appear as separate choices in a validation list?

I have tried every combination I could think of using arrays, named lists, the INDIRECT function, etc. I cannot seem to get it to recognize them as separate items. For the moment, I have created a bad (too long/complex) workaround where formulas are used to pull out each of the 5 strings into individual cells.

If necessary, I could explain further why I need to validate in this way.

Thank you all in advance for your help,

Matthew

Hello,

I hope the title makes sense. I want to split data from a single cell into multiple cells. Each cell on a row has different items listed. Some have 14 items, other 2 or 3, in the same cell. The only item seperating each listed data is a semicolumn. I did "text-to-columns" but I can only separate the first item listed. Here's an example of what it looks like.

I've been searching everywhere for the last hour and just can't find anything. I appreciate any help you can provide.

Thank you.

I need help with a formula. I need to add different numbers from a single cell. For example I need to be able to enter an 8, delete the 8, then enter an10 in the same cell and the total to come up as 18. Any help would be greatly appreciated.

I have a database I'm pulling address information from that puts the city, state and zip all in one field. So the field looks like this:

LAS VEGAS, NV 89131-1234

I just need to pull the 5-digit zip codes from this field. Any hints on the simplest way to accomplish this?

Thanks in advance!

Les :^)

I've been copying some code from a pdf file where all the apostrophes are on an angle, so I have to correct them before they can be read by VBA.
For some reason the following macro (mostly done using the recorder) only
works correctly some of the time, other times the apostrophes are removed
entirely (I generally copy the code into the worksheet by pasting in cell A1.)
Any ideas as to why this would happen please? (And is there a better way to do this, maybe by editing the pdf file?) Thanks!

Cells.Select
    Selection.Replace What:="“", Replacement:="""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Selection.Replace What:="”", Replacement:="""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
     Selection.Replace What:="‘", Replacement:="'", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D8").Select  
    Range("A1:c200").Select

    Selection.Copy


I have a system that downloads into excel only it puts all of the data into one clomun and I need to have it separate. The fields are employee addresses. The software takes the employee street #, name, city, state, and Zip code off a resume and merges it into one column. I need to split out the City, state, and Zip into three separate columns and leave the rest alone. I tried to text the column, however since all of the addresses contain different numbers of characters or spacing, it is not working.

Any ideas?

Hey,
I have to do a maass mailing.
I have a spreadsheet of the Name, Address, City, State and Zip code each in different cells.

I was wondering How I can take the info. from 1 person, (1 row; name, address, city, state, zip) and put it according in the word document, which is a letter template.

thanks.

I have a big problem. I have a list of customer numbers that want a catalog in an Excel Spreadsheet. I have a list of all of our customers in an Access database. How can I get the customers' names, addresses, cities, states, and zip codes from the database to the spreadsheet so that the correct names line up with their numbers so that I can only send the ones who want a catalog, and not everybody in the entire customer database? Please make this as simple as possible, because I'm not very well versed in Access.

Thanks
Mark Winberry

I have a column that sometimes has multiple values in a single cell. I want to pull the unique values from that cell using a formula.

Example
Column A

2) Dog
3)Cat
4)Bird,Bird,Cat,Dog
5)Frog,Dog,Frog,Frog,Frog
6)Mouse
7)Monkey

In cell B2 I want Dog, Cell B3: Cat and B4: Bird,Cat,Dog, B5:Frog,Dog

I want a formula I can type in B and have it look to the Cell and extract unique values

Hi there,

I am looking for a solution to perform the following task:

I have a spreasheet formatted like a form, which users insert data into. The data will be entered into one column but an undefined number of rows, as users will be able to create additional rows. i.e A1, A2, A3 . . . Ax

I know the start cell A1. The data will contain text only.

I then need to take this text data from multiple rows and concatenate it into a single cell and format it in the style of 1) <rowdata1> 2) <rowdata2> 3) etc..

I have managed to work the dynamic array as a sum(calculation) based on example from a book but can I do the same with text?

Hope someone can help!

I have a large database with a lot of data stored in a single cell (Well actually it was a comment in that cell, but I pulled it to a cell)

The Data Looks Like This in A1:

Line 1
Line 2
Line 3
Line 4

I would like to know how to get that data to print like this

B1 = Line 1
C1 = Line 2
D1 = Line 3
E1 = Line 4

The amount of data varies from cell to cell, but never more than 20 or so lines. I have searched and could not find anything. Search skills must be lacking this morning.

Thanks

First, thank you in advance to everyone who reads this and can offer any assistance. I’d classify myself as OK with Excel in terms of using formulas but pathetic when it comes to VBA. Which is what leads me here…

What I am trying to accomplish, using Excel 2003, is as follows:

User Enters a part # - Data is looked up and pulled based on this part # (Brand, Style, Description). This is done using simple VLookups and I have that taken care of already.

Now the problem that I run into is because I am creating this using a data table provided by a vendor, so I don’t have the data formatted in a way that’s ideal to create this. For example, what I have to work with is a single cell full of the following text:

NEUTRALS - White(3X); HEATHERS - Dark Heather(3X), Sport Grey(3X); COLORS - Azalea, Black(3X), Charcoal, Cherry Red, Chestnut, Dark Chocolate, Heather Dark Chocolate(3X), Heather Maroon(3X), Heather Navy(3X), Heather Sapphire(3X), Irish Green, Kiwi, Light Blue(3X), Light Pink(3X), Navy(3X), Orchid, Red(3X), Royal(3X), Sky, Purple(3X)

The above info will change depending on the part # selected but the formatting remains as above. I may need to have the data looked up as part of the macro because in my attempts to manipulate the cell with the lookup formula in it using things like convert text to columns, Excel typically tries to separate the formula rather than the data contained within it.

Anyway, the meat of what I need is this:

The categories pulled out from the text cell. The categories are in all caps, “NEUTRALS”, “HEATHERS” etc. These would be placed into a column, each in their own row to form a drop-down.

The Color options pulled out from that cell, preferably without the portion in parenthesis afterwards, but I can live with it. These also placed into a column, each entry with its own row to form a dropdown.

I’m not sure if what I’m asking for is even possible, and if its not that’s fine and I’ll try to come up with a workaround.

Let me know if you need more information on what it is I’m trying to accomplish and I’ll do my best to provide it.

Thanks again,
Aaron

Hi,

I am looking for a formula to lookup multiple values and return them in a single cell seperated by commas.
A sample of the data is as follows - there are 3 columns:
Code, Batch No, Location
1,2,A1
1,2,A2
2,1,B3
3,3,C1
1,2,A1
1,1,A2

If i entered the formula in a cell for code 1 and batch no 2 I would like it to return A1,A2. I have been using vlookup and if however it only returns the first value not multiple. Can anybody help me?

Many Thanks.

Assuming you would juste have words (those words can contain spaces) separated by a comma in a single cell : apple, George W. Bush, kiwi

How can I find the word after the 2nd comma? (i.e. kiwi)?


No luck finding an answer? You could always try Google.