|
Splitting Cells With Commas
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I have inherited a spreadsheet listing clients' addresses. Each client's full address is written in just one cell and separated by commas (e.g. 1 High Street, Brighton, East Sussex, BN1 1AB). What I would like is for each component of the address (i.e. 1 High Street) to be listed in its own individual cell. Is there any easy way to do this, as I'm a bit stumped at the moment!
Thank you
Similar Excel Video Tutorials
Array Formulas Basics
- Topics for this video: 1)Learn about the basics of array formulas 2)Entering array formulas with Ctrl + Shift + Enter 3)Array syntax
Similar Topics
Hi,
I have a worksheet full of text such as:
1 The High Street, Anytown, Anywhere, ZIP
There may be three, four or five elements to the address which are all
separated by commas. I would like to separate these into individual elements
(so I can import them to Access). I have the functions to strip out the first
and last component but I'm really struggling with the otheres.
Any help gratefully accepted.
PDJ
--
If nothing changes, everything stays the same
I have an issue with a lookup formula. I am trying to create a sheet where a front end user can input a Colum or two of their location (ie. Street and number) and it return the further information, to provide a code to them they would not know. My issue is as there is no unique identifier im getting confused.
As per the example belwo (cant seem to attach a file?! maybe as new user?) I want people to be able to input as little as possible to return the full row of information.
Number
Street
Area
Code
1
Main Street
West
MSW
2
Main Street
West
MSW
3
Main Street
West
MSW
4
High Street
North
HSN
5
High Street
North
HSN
6
High Street
North
HSN
1
Queens Stree
South
QSS
2
Queens Stree
South
QSS
3
Queens Stree
South
QSS
4
Kings Street
East
KSE
5
Kings Street
East
KSE
6
Kings Street
East
KSE
E.G. I live at 1, main street, west. But I cant simply input 1 as there are two addresses with a 1 that would appear or just main street as three address match this, and so on.
So can I get a function that will match 2 columns and return the whole row of information.
E.g. I type in that I live at 1 so it then suggests that this could pertain to Main Street or Queens Street but not the others as none have a number 1. Then I select which and it will then highlight to me that I am 1, main street, west.
(If that makes sense?!?)
Hope you can help!
I know this should be really easy, but I can't figure it out. I have a list of 400 addresses that my client wants in numberical order from lowest address (3 Street) to highest address (1001 street).
When I hit sort, it sorts them by 1's, then 2's, then 3's, etc. How can I get it to recognize that 3 Street is lower than 1001 Street?
Thanks!!
Hi, Hope someone can help.
My aim is to compile a mailing list so that only one letter is sent to each address. The mailing list should contain both the customer's name and address.
Starting at N2 down is a list of addresses. In M2 down is a list of customer names. I need a formula that will look down the addresses and match it to the first name ONLY at that address, eliminating all others. So for example the expected output for the following data:
Fred Bloggs...23,Main Street
Jim Bloggs ... 23,Main Street
Bob Bloggs ..23,Main Street
Paul Hobb ...14,High Street
Mat Brown... 16, Railway Road
Nic Brown ...16, Railway Road
should be
Fred Bloggs...23,Main Street [Jim & Bob Bloggs ignored as at same address]
Paul Hobb ...14,High Street [this is a unique address and so is included]
Mat Brown... 16, Railway Road [Nic Brown ignored as at same address]
Basically a list eliminating duplicate addresses. A formula I can copy down would be ideal.
Many thanks in advance.
Hello
I have the following problem which I hope someone can please help me with!
I have an address field in my spreadsheet as one cell e.g.
15 High Street, London, W1T 6YH
However, I need the cell split into separate cells (i.e. street, town, postcode), and I was wondering if there is any way that Excel can be told to split the cell each time it find a , ?
Many thanks for your help.
Best wishes
Jon
Good Evening Everyone,
I have read many of the threads here and am amazed at the information presented. I finally decided to register and post the challenge I am working thru.
Every Week I get a sheet from Corporate with over 5,000 clients, only 1,000 of those are mine. I want to update the information contained in the Corporate sheet to My Sheet, so that I don't have to sort my clients out each time and can keep my notes. Each Client has a Unique Client Id (100, 101, 102 etc). (see example below)
I can move the columns around a bit in both sheets but other than that as the sheet comes from corporate things are locked. Column headings and client ID's are a constant.
I'm beating my head against the wall trying to compose a IF THEN or a macro. Any ideas?
Thanks Everyone!!
Corporate Sheet:
CLIENT ID CLIENT NAME ADDRESS
100 John Main Street
101 Paul Smart Street
102 Ethan Simple Street
103 Nathan Near Street
My Sheet:
CLIENT ID CLIENT NAME ADDRESS NOTES
100 John Carp Street Good Client
101 Paul Smart Street Leaving Soon
103 Nathan Sunny Street Moved
so using this example I want MY SHEET and my specific clients information to be updated from the Corporate Sheet and keeping my notes.
i have been looking for a way to put a space between the street number and address and then move the street number and address to seaparate colums and i come up with nothing pertaining to my situation. i need some serious help.
i attached the excel sheet with a few address from a much larger workbook of 4300 addresses. as you see i am working on a big project, so i would greatly appreciate some feedback
I am working on a home PC, Excel 2003. I have a column (it is column C of A
thru K, if that matters) of data that includes street address (may include
suite number, etc.), city, state, zip and all info is separated by commas
except state and zip. I am trying to separate the information in this column
so that each part is in its own column. I need to keep all of the address
(street number, street name, PO box number, suite number, etc. together) in
one field, city in the next field, then state, then zip. The problem I am
having when converting text to columns using the comma as the delimiter is
that I end up with too many columns of data. Is there a way to do something
like this starting with the comma farthest to the right and only going back
five characters to get the zip out and then continuing from right to left for
two commas to strip the state out then the city, leaving the rest to be the
address?
Any help is greatly appreciated. My only alternative is to retype all info
in columns A through K, rows three through 1,102.
--
God Bless! Cindy
I am currently a police officer in the UK and I am trying to device a
spreadsheet / database that will highlight problem hotspots.
Now, I intend to create a spreadsheet that will highlight areas that are
affected by different crime. So, in one sheet I will have burglaries, car
crime in another, antisocial behaviour in another.
Now I am unsure what data the spreadsheet should contain and how to display
the results. I would like the results to come back on an individual name /
address basis per sheet in the file, but I would also like it to produce
results on road names.
So for example, if Mr Jones lives in 123 High Street, I would want the
spreadsheet to highlight it if Mr Jones gets burgled on two or more
occasions. Nonetheless, I would also like some results produced if two or
more burglaries are recored in High Street, regardless of the house number.
How would I go about this? Can I search cells in a column for specific
words, so in the above case, "High Street" regardless of number?
Many thanks
I've inherited a workbook with data like:
A1 = 1 The Street B1 = Anytown
In C1 if I use A1&" "&B1 I get 1 The Street Anytown
What I need in C1 is:
1 The Street
Anytown
Other info - The example shows just two of seven address lines that need to be concatenated into one cell. There are approximately 200 addresses to be manipulated.
Thanks
Hi,
If I have a column full of email addresses in Excel 2007 and I want to copy that column and paste it into a website that only accepts the email addresses if they are all separated by commas how do I do that without manually adding commas between each email address?
Thanks!
Adrian
Friends,
Here is the basic situation. I have about 10 excel sheets containing addresses of thousands of contacts. They are written like this:
[Prior Title][First Name][Middle Name][Last Name][End Title][Street Address Line 1][Street Address Line 2][Street Address Line 3][City, State Zip]
Not all cells, like titles, contain data for all persons. Anyway, what I need is a way to insert, automatically, these addresses into the proper address space in a prewritten letter and on an envelope. I know how to merge to create address labels in word, but not merge them into a completed letter, as in:
Word File:
===
December 7, 2005
[Prior Title][First Name][Middle Name][Last Name][End Title]
[Street Address Line 1]
[Street Address Line 2]
[Street Address Line 3]
[City, State Zip]
Dear [Prior Title][First Name][Last Name][End Title]
How are you, blah, blah, important stuff, blah.
Sincerely,
TheOne
============
So, one letter, thousands of different addresses. What do you folks think I can do to achieve this? Any suggestions, except for how to make normal address labels, would be greatly appreciated.
Thanks,
TheOne
Hi!
I've been dealing with this headache for over 2 hours now, and I was hoping maybe some of you can help me out.
I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.
So in the end it would go from: (sorry for the periods.. extra spacing didn't work!)
A........B................C.......D
AAA...123 Street...City...<blank>
BBB...456 Street...City...<blank>
CCC...789 Street...City...<blank>
To:
A........B................C.......D
AAA...123 Street...City...1
AAA...123 Street...City...2
AAA...123 Street...City...3~
AAA...123 Street...City...150
BBB...456 Street...City...1
BBB...456 Street...City...2
BBB...456 Street...City...3~
BBB...456 Street...City...150
CCC...789 Street...City...1
CCC...789 Street...City...2
CCC...789 Street...City...3~
CCC...789 Street...City...150
I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it
I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.
Hopefully I made a little bit of sense of what I'm trying to do/achieve..
Thanks ahead of time!
Hi - I'm running 2007, and have a mailing list that is setup - A1 = text(full name of company), A2 = text(full street address, city, state, zip) NO PUNCTUATION.
I have been able to separate each word for the address line, but this leaves me with many different length addresses spread out over different cells, so taking just the city state and zip is not feasible in large numbers. I have over 10,000 of these listed down column A1.
I need to be able to extract city, state, and zip, (There is no punctuation in the address line)and put them all in one cell, for the third line of the address box. I need them to cut from the source, also - leaving the source only the street address.
I would greatly appreciate any help on this. Thanks
wheels
I have a list of names and addresses as follows:
700 Main Street
I would like to parse the information with street name
( Main Street ) in one field and street address ( 700 )
in a separate field.
Street number and street name are varied in length
throughout the list.
Is it possible to parse everything to the left or right of
the space?
Thanks
I have a spreadsheet containing hundreds of rows of info like this:
MICKEY MOUSE 23 Smith Street, Bolton, Exeter CH45 1LP
How can I split it up into 5 or more columns to look like this:
Name
Address 1
Address 2
Address 3
Address 4
MICKEY MOUSE
23 Smith Street
Bolton
Lancashire
CH45 1LP
(I dont need the titles row eg "address 1" in the result by the way)
I notice there are always 2 spaces in the original cell, after the name and before the address, if this helps at all. Any suggestions?
164 Canal Street, NY, NY 10013
The information listed is in once cell and i need to separate it into cells for street address, city, state, zip.
I have done it in the past and cannot rememeber. I remmber starting the formula with =Left( and =Right(
but for the life of me I cannot remember. Please help ASAP! Also I need it to work over multiple cells. so basically I need it to pull out the first info up to the comma. Then a forumal to start at comma and pull out the info up to the next comma, etc
Thank you so much
Hello all,
It's been a long time since my last post. I am hoping someone can help me with the following question. I have an excel spreadsheet database displaying 5.000 contact information such as my example below:
Title FirstName LastName Address
Mr adulted it is me 144 picton street e
Ms Moe Scally 1343 university court
What I am trying to do is put 144 in its own column to the left of address and the street name (picton street e) in its own column or the street name to the right of the address column.
Or as in the second example What I am trying to do is put 1343 in its own column to the left of address and the street name (university court) in its own column or the street name to the right of the address column.
In simple terms, this 5,000 enrties need to be sorted by street name only, exluding numbers, possible PO Box, or RR # 3, etc...
Thanks in advance,
Paul
Hi there,
I've about 50,000 rows of data and an address in every row thats been entered into a single cell by using Alt+Ent
Sample of cell
3 Sample Street
Sample Town
Samplesville
(Postcode is already split into own column)
The number of lines in each address varies and there are no commas etc.
Cheers, Rich
I have a spreadsheet that has about 100 tabs. Each tab represents one person and their personal info, i.e. Name, Address, Phone Number... What I need to do is create a new spreadsheet that contains everyones Name and Address on one tab.
Currently, the data is like this, one person per tab:
A
2 First Last
3 Street Address
4 City, State Zip
What I need to turn it into is this, with everyone on one tab.
A B C
2 First Last Street Address City, State Zip
3 First Last Street Address City, State Zip
4 First Last Street Address City, State Zip
5 First Last Street Address City, State Zip
6 First Last Street Address City, State Zip
Is there a way that I can run a macro to move from tab to tab and pull the data from the required fields?
Thanks,
Jeff
Hi there
I have inherited in a new job a spreadsheet that is basically a Suppliers Database.
The address when you look at it goes from left to right - after the company name there are 2 bold vertical lines, same after the street name,town,postcode etc.
When you click on this cell the data then looks like an address label ie name on one line, street name on the next etc.
How Do I do This??? What are these vertical lines???
Any help much appreciated.
Mark
Hello,
I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.
Any ideas?
Thanks,
Sharjeel
I'm trying to "split" content in a cell. Someone without much Excel experience has typed an entire mailing address in one cell, i.e. 123 Main Street, Ste. 100, City IL zip. Is there a way to split them? I've used the Data/Test to Columns feature. The problem is some of the cells contain commas, some don't, some contain more info than others, like P.O. Box as well as the street address, some have city, some don't. Any ideas?
Good morning,day,afternoon,evening everyone.
I have a list of client addresses about 12000 rows long, I want to see how many clients live on a certain street... Like, to get a counter listing how many live on Main St. I'm assuming this is a variation of the countif formula...? I just don't know how to read PART of a cell.
I have a spreadsheet with several hundred addresses on it. columns A is the resident's name; column b is their house #, column c is their street name (Maple); column d is their street type (Dr., St., Ave., etc...) column e is their city, etc.... You get the picture.
Problem is, there are in some cases more than one person listed at each address, such as a husband is on row 700 and his wife is listed on row 701 and all of the "address" information is identical.
What I need to do is filter this list in such a way that all when I merge this spreadsheet into mailing labels, I am only sending one piece of mail to each address (this is the "unique record" part of the filter) and not sending the same piece of mail to both the Mr. and the Mrs. at the same address.
How can I get excel to filter these records, comparing the house #, street name, street type, city, state, zip, etc..., and throw out all duplicates. (By the way, I don't care if it keeps the Mr. or the Mrs. -- the first one that shows up is fine with me.)
Please help!!!
|
|
