| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how can I make info in one cell appear in 3 different columns?
I'd like to take something like this out of one cell:
White Plains, New York 10604 and make it appear in 3 different columns, is it possible? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how can I make info in one cell appear in 3 different columns?
Assuming the city is always separated from the state by a coma space and the
the zip code is always a 5-digit number, then if your entry is in A1, put the following formulas in B1, C1 and D1... B1: =LEFT(A1,FIND(",",A1)-1) C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,"")) D1: =RIGHT(A1,5) Rick "Joe" wrote in message ... I'd like to take something like this out of one cell: White Plains, New York 10604 and make it appear in 3 different columns, is it possible? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how can I make info in one cell appear in 3 different columns?
Select the column & then
Go to Data | text to columns | Click Fixed Width | next | finish On Apr 20, 8:26*am, Joe wrote: I'd like to take something like this out of one cell: White Plains, New York 10604 and make it appear in 3 different columns, is it possible? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how can I make info in one cell appear in 3 different columns?
Rick,
Regarding the equation to extract the ZIP code, how would you retain a leading zero as in Attleboro, MA 02703? Thanks John L "Rick Rothstein (MVP - VB)" wrote: Assuming the city is always separated from the state by a coma space and the the zip code is always a 5-digit number, then if your entry is in A1, put the following formulas in B1, C1 and D1... B1: =LEFT(A1,FIND(",",A1)-1) C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,"")) D1: =RIGHT(A1,5) Rick "Joe" wrote in message ... I'd like to take something like this out of one cell: White Plains, New York 10604 and make it appear in 3 different columns, is it possible? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how can I make info in one cell appear in 3 different columns?
Format Column D as Special/Zip Code would be one way (although on my system,
the leading zero is preserved directly from the formula with the cell Formatted as General). Rick "JohnL" wrote in message ... Rick, Regarding the equation to extract the ZIP code, how would you retain a leading zero as in Attleboro, MA 02703? Thanks John L "Rick Rothstein (MVP - VB)" wrote: Assuming the city is always separated from the state by a coma space and the the zip code is always a 5-digit number, then if your entry is in A1, put the following formulas in B1, C1 and D1... B1: =LEFT(A1,FIND(",",A1)-1) C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,"")) D1: =RIGHT(A1,5) Rick "Joe" wrote in message ... I'd like to take something like this out of one cell: White Plains, New York 10604 and make it appear in 3 different columns, is it possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| is there a way to make info from one cell migrate to another cellautomatically? | Excel Discussion (Misc queries) | |||
| I HAVE SAY INFO ACROSS 10 COLUMNS. HOW TO MAKE THE COL-A CELL ACTIVE WHEN U HIT 'ENTER' | Excel Discussion (Misc queries) | |||
| too much info in one cell in excel how do I make it 2 lines? | Excel Discussion (Misc queries) | |||
| How do you make 3 stacked columns of info for each date in Excel | Charts and Charting in Excel | |||
| When sorting info in columns, can I make it insert blank line bet. | Excel Worksheet Functions | |||
Linear Mode
