Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I need to breakup an address in a cell...............
What is the formula for breaking up the contents of a cell into multiple cells.
A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
#2
|
|||
|
|||
Hi
Select the cell, then click on Data|Text to Columns, Select delimited and Space as the delimiter, and Finish -- ve_2nd_at. Randburg, Gauteng, South Africa "Tony" wrote: What is the formula for breaking up the contents of a cell into multiple cells. A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
#3
|
|||
|
|||
The feature you are looking for is called Text to Columns. Just select the
cell(s) and pull-down: Data Text to Columns... and let the Wizard guide you. Good Luck -- Gary's Student "Tony" wrote: What is the formula for breaking up the contents of a cell into multiple cells. A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
#4
|
|||
|
|||
Try this:
in cell B1 - =LEFT(A1,FIND(",",A1,1)-1) in cell B2 - =MID(A1,LEN(B1)+3,2) in cell B3 - =RIGHT(A1,5) "Tony" wrote: What is the formula for breaking up the contents of a cell into multiple cells. A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
#5
|
|||
|
|||
If you have thousands of them I would dump them into Word. Convert table to
text. replace all commaspace with just comma. shoot I was going to tell you to replace all spaces with commas but that place commas with town names. Then convert text back to table and dump it into excel. For those towns with spaces. it would push the zipcode into a separate column. Sort by that column and make repairs to that area. "Tony" wrote: What is the formula for breaking up the contents of a cell into multiple cells. A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
#6
|
|||
|
|||
Here's a couple of functions to get you started...
City: =LEFT(A1,SEARCH(",",A1,1)-1) State: =MID(A1,SEARCH(",",A1,1)+2,2) Zip: =RIGHT(A1,5) 'if only 5 characters hth James "Tony" wrote: What is the formula for breaking up the contents of a cell into multiple cells. A1 Detroit, MI 48089 A1 B2 B3 Detroit MI 48089 Thanks for the help........... Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) | |||
up to 7 functions? | Excel Worksheet Functions |