Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How 2 separate column of addresses into a colum of names and numb
Hi!
I would like advice on how to separate the names and the numbers of an address written in one column into the name and the number in two separate columns. In otherwords "Kingstreet 23" must become "Kingstreet" and "23." Can anyone help with a function that can do this? Thanks in advance, Rochelle |
#2
|
|||
|
|||
Hi Rochelle
if all your names & numbers are similar to your example, then data / text to columns will probably work for you ensure that you have a couple of blank columns to the right of your current column (note, this process will split up your original column into two and won't retain a copy of the original - if you want a "combined" column, copy this column to a new column first). select the column to be split up, choose data / text to columns, choose deliminated choose NEXT, untick TAB, tick SPACE, click FINISH. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rochelle" wrote in message ... Hi! I would like advice on how to separate the names and the numbers of an address written in one column into the name and the number in two separate columns. In otherwords "Kingstreet 23" must become "Kingstreet" and "23." Can anyone help with a function that can do this? Thanks in advance, Rochelle |
#3
|
|||
|
|||
One way:
In place (e.g., A1 - A1:B1): Choose Data/Text to Columns. Click "Delimited". Click Next. Check the Space checkbox, click Finish. By formula: A1: Kingstreet 23 B1: =LEFT(A1,FIND(" ",A1)-1) C1: =MID(A1, FIND(" ",A1)+1, 255) In article , "Rochelle" wrote: Hi! I would like advice on how to separate the names and the numbers of an address written in one column into the name and the number in two separate columns. In otherwords "Kingstreet 23" must become "Kingstreet" and "23." Can anyone help with a function that can do this? Thanks in advance, Rochelle |
#4
|
|||
|
|||
Thanks to both of you!!
Rochelle "JE McGimpsey" wrote: One way: In place (e.g., A1 - A1:B1): Choose Data/Text to Columns. Click "Delimited". Click Next. Check the Space checkbox, click Finish. By formula: A1: Kingstreet 23 B1: =LEFT(A1,FIND(" ",A1)-1) C1: =MID(A1, FIND(" ",A1)+1, 255) In article , "Rochelle" wrote: Hi! I would like advice on how to separate the names and the numbers of an address written in one column into the name and the number in two separate columns. In otherwords "Kingstreet 23" must become "Kingstreet" and "23." Can anyone help with a function that can do this? Thanks in advance, Rochelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
formula to calculate a column based on dates in a different colum. | Excel Worksheet Functions | |||
10,000 addresses in column A; divided into 4 sections across. | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
How do you sort a column of email addresses by domain name in Exc. | Excel Discussion (Misc queries) |