| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can text in one cell be split into several cells?
I have a sheet with about 10,000 names and addresses in it in which the city
and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
You can use text to column and split them on the basis of the space between
the city and state. -- Brevity is the soul of wit. "Mistie" wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
Well, actually, I tried that. However, there are some cities that have two
or three names, and they mix with the states that only have one. So, that doesn't work. Thank you though. Mistie "Dave F" wrote: You can use text to column and split them on the basis of the space between the city and state. -- Brevity is the soul of wit. "Mistie" wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
Have you tried using text to columns with the other check box selected
and using a space as the delimeter? Mistie wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
I tried to do that since there seems to be 3 spaces between the city and
state. But, it did not take 3 spaces (or any spaces) in the other box. Thank you for trying. Mistie "Sandy" wrote: Have you tried using text to columns with the other check box selected and using a space as the delimeter? Mistie wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
Mistie, is the State a name or a 2 letter code at the end of the text string?
-- Sincerely, Michael Colvin "Mistie" wrote: I tried to do that since there seems to be 3 spaces between the city and state. But, it did not take 3 spaces (or any spaces) in the other box. Thank you for trying. Mistie "Sandy" wrote: Have you tried using text to columns with the other check box selected and using a space as the delimeter? Mistie wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
It is a letter abbreviation at the end of the text of that cell. It would
not be such a problem except that the cities are different word lengths, so they do not split correctly. I can write a function to extract the states into their own column, but then I still have it listed in the other column still. I am out of ideas. Please help if you can. Thank you, Mistie "Michael" wrote: Mistie, is the State a name or a 2 letter code at the end of the text string? -- Sincerely, Michael Colvin "Mistie" wrote: I tried to do that since there seems to be 3 spaces between the city and state. But, it did not take 3 spaces (or any spaces) in the other box. Thank you for trying. Mistie "Sandy" wrote: Have you tried using text to columns with the other check box selected and using a space as the delimeter? Mistie wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
On Fri, 8 Sep 2006 13:31:01 -0700, Mistie <Mistie @discussions.microsoft.com
wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. Assuming the state is represented by a two letter abbreviation, and your data is in A1, then City: =LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) State: =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),2) --ron |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
Oh, I knew there had to be a formula for this....I just wasn't smart enough
to figure it out. I put it in and it worked perfectly. Thank you, thank you, thank you!! Mistie "Ron Rosenfeld" wrote: On Fri, 8 Sep 2006 13:31:01 -0700, Mistie <Mistie @discussions.microsoft.com wrote: I have a sheet with about 10,000 names and addresses in it in which the city and state were entered into one cell. I have tried all day to find a function or way to split them up. There are no commas in between and they are not lined up to be able to use text to column. I need help, please? Thank you. Assuming the state is represented by a two letter abbreviation, and your data is in A1, then City: =LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) State: =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),2) --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can text in one cell be split into several cells?
On Sat, 9 Sep 2006 19:09:01 -0700, Mistie
wrote: Oh, I knew there had to be a formula for this....I just wasn't smart enough to figure it out. I put it in and it worked perfectly. Thank you, thank you, thank you!! Mistie You're welcome. Glad to help. Thanks for the feedback. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| merge several cells with text into 1 cell | Excel Discussion (Misc queries) | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| How do you write 2 lines of text in 1 cell rather than use 2 cells | Excel Discussion (Misc queries) | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Hybrid Mode
