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
0
Rochelle (22)
4/25/2005 4:22:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
378 Views

Similar Articles

[PageSpeed] 1

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" <Rochelle@discussions.microsoft.com> wrote in message 
news:3CD72DB4-B572-4557-950E-226ECD54CF67@microsoft.com...
> 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 


0
JulieD1 (2295)
4/25/2005 4:38:45 PM
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 <3CD72DB4-B572-4557-950E-226ECD54CF67@microsoft.com>,
 "Rochelle" <Rochelle@discussions.microsoft.com> 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
0
jemcgimpsey (6723)
4/25/2005 4:41:40 PM
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 <3CD72DB4-B572-4557-950E-226ECD54CF67@microsoft.com>,
>  "Rochelle" <Rochelle@discussions.microsoft.com> 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
> 
0
Rochelle (22)
4/26/2005 9:02:01 AM
Reply:

Similar Artilces:

Don't Want Email Addresses Hyperlinked
Using Excel 2002... Is there any way to have the email address I have in my worksheet remain as simple text? (They typically are blue and want to be "mailtos.") thnx Menu Insert|Hyperlink... Click the Remove Hyperlink button. HTH Kostis Vezerides Tools / AutoCorrect Options.... "Autoformat as you type" tab, uncheck "internet and network paths with hyperlinks" HTH, Bernie MS Excel MVP "phil6666" <phil6666@adelphia.net> wrote in message news:2tn6o194usnq8dtdr7g7nfciv2pd68ccrc@4ax.com... > Using Excel 2002... > > Is there any ...

Viewing IMAP messages #2
I connect to a Linux IMAP server remotely for my emails. When I click on an email in Outlook P once the headers have been updated the system seems to download the email and the attachement. All I want to do is read the message before I download the attachment - Is this possible? Its a bit of a problem if an email has a 3mb attachement and all I want to do is read the mail content before deciding to download the attachment - especially a pain if I am CC into some mail. Regards Andy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Ve...

Sumproduct #2
Hi all! I am trying to use Sumproduct with dates that come off an SQL databas and are in the format of 02/12/2003 15:30. This is forcing me to use the following formula =SUMPRODUCT((Data!$E$2:$E$3893>G$3)*(Data!$E$2:$E$3893<G$2)*(Data!$C$2:$C$3893=$A4)*(Data!$G$2:$G$3893)) where $G3 is 02/12/2003 00:01 and $G2 is 02/12/2003 23:59 Is there any way I can just look at the date only rather than the time as well, so that anyone can enter a date from, and a date to In anticipatio ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and p...

pasting into merged cells #2
Every time I try to copy and paste data into a merged cell I get error messages about the cells not being the same size. Even if I try to just paste values only. Is there any workaround or fix for this??? Dennis Try pasting into the formula bar. It's a pain, but it works. HTH Carole >-----Original Message----- >Every time I try to copy and paste data into a merged cell I get error >messages about the cells not being the same size. Even if I try to just >paste values only. Is there any workaround or fix for this??? > >Dennis > > >. > See my res...

Work out overtime hours for individuals #2
Hi All I have a question which i am hoping someone will eb able to help me with, i have a spreadsheet which contains infromation for part time hours worked during the course of a month for individuals (e.g. below) Name Start End Hours Persona 1:30 2:30 1 Persona 3:30 4:30 1 Persona 9:30 11:30 person b person b person c person c person d etc etc What i need is a formula that will allo wme to add an extra column which gives me the total hours person a worked, person be worked and so on. The original data contains multiple lines (i.e persona could have 10 lines, personb15 lines etc etc), and...

add contents of one column
I have a budget that I want to automatically add the contents of one column as I'm working on the sheet. I am new to this and I need all of the helo that I can get. In B1: =SUM(A:A) -- HTH RP (remove nothere from the email address if mailing direct) "Pam" <Pam@discussions.microsoft.com> wrote in message news:FC5F8EE6-99D4-4327-B452-D34E441CA743@microsoft.com... > I have a budget that I want to automatically add the contents of one column > as I'm working on the sheet. I am new to this and I need all of the helo that > I can get. Assume your numbers ...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

MS Office VBA Automation Specialist #2
Hi there, is there such a certificate? MS Office VBA Automation Specialist, I tried googling it on microsoft website and couldn't find any info. I just passed the excel 2k expert exam and I want to take the excel instructor exam, I haven't however been an instructor. is it possible to take the exam without an instructor experience? last but not least, I am thinking about putting together some excel webcasts for beginners for free. I have always liked to learn by audio/visual versus reading so I am thinking about implementing this... here is the link for the first test webcast ...

Address Book and other questions?
I have the following questions: How do I organize the address book to look like it does in Outlook - i.e. - detailed address cards? Can I change the color palette in Entourage? "Craig Schiffer" wrote: > How do I organize the address book to look like it does in Outlook - i.e. - > detailed address cards? I've never used Outlook so I couldn't even guess at how it looks. You need to post screenshots and give details of what you want the Address Book contact to show. You can post a screenshot link using a service like ImageShack. <http://imageshack.us/> How To...

Making cards --2 to a 8 1/2 x 11 page
I want to make cards to send out for memorials, etc. I want 2 cards to a page, top folded 5 1/2 x 8 1/2. What happened to the old Picture It! Publisher 2001? Do you want to make these cards in Publisher? If you do, look at page setup, folded card, tent card, width 4.25, height 5.5. Publisher will say it will print two pages per sheet -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "A Holt" <A Holt@discussions.microsoft.com> wrote in message news:F5F75EEF-B186-4960-9AEB-AF1C678492B7@microsoft.com... >I want t...

Last name, first name
I have a list with lastname, firstname in a cell. I would like to change it to firsname lastname. Any clues? officexp. Thank you. Mich - Insert a column after your name column, highlight the name column, go to Data, Text to Columns, Delimited, select comma, finish. This will give you two columns, lastname, firstname. Insert a column before your lastname column. Move the firstname column to the new column. Your third column should now be blank. Enter in the formula =CONCATENATE(,A1," ",B1)in the third column. This will put your firstname space lastname in the third ...

how to arrange list by first or last name
I am trying to make a list with first, middle, last name , street address, and phone numbers. I like to know how you can review the list by any combination of first name, last name, city, state, and or phone number? hi can you give examples of how your list is layed out. i am thinking a helper column with an extraction formula might work. regards FSt1 "Andy" wrote: > I am trying to make a list with first, middle, last name , street address, > and phone numbers. I like to know how you can review the list by any > combination of first name, last name, c...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Download email #2
How to download email from Microsoft exchange 2000 to Outlook Express?? without leave a copy at Exchange server. Outlook Express cannot download mail from Exchange. Why are you trying to do so without leaving a copy of the mail on Exchange? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Edi asked: | How to download email from Microsoft exchange 2000 to | Outlook E...

How do I export email addresses from excel to outlook?
I am trying to do a mail merge using email via outlook. I have 200+ addresses and I'd like to know how to import the addresses into the contacts section of outlook to do the merge from there. I've tried the help part of out look but it comes up saying that the excel file has no named ranges and that I should use excel to name the range of data to be imported. Any help much appreciated. Thanks Mark In outlook select file/import export/ import from another program or file/ and then follow the instructions from the wizard. Why do you want to do the mailmerge from Outlook? You can...

CF to Row after CF is applied to Column
Can someone please help me? I've read through the postings and the help files but still can't seem to figure out how to do this. I've applied conditional formatting to the J column of my spreadsheet so that if there's any text listed the cell turns yellow. What I'd like is if not only the cell but the row, from A:O turned yellow also. How do you do that? I'm using 07. Any help in English walking me through the steps or a hint toward the appropriate post would be greatly appreciated. Cuz I aint figrin it out on myown! Thanks in advance! --Dax...

Windows 2008
Hi, I am an admin in windows 2008 server. However, I am not able to access the c:\Users\<user name>\Local Settings\History Anyone knows how to change the settings so I can access the folder? Thank you, "Jack Black" <gwklocker01@gmail.com> said this in news item news:60b578d9-ce73-4ab8-851b-9039ea6cfee5@n16g2000yqm.googlegroups.com... > Hi, > > I am an admin in windows 2008 server. However, I am not able to > access the > c:\Users\<user name>\Local Settings\History > > Anyone knows how to change the settings so I can...

Naming new server
Cana anyone tell me if there are any implication with Exchange 2003 with having the server physically named "xyz.company.com" and having a cname in the internal dns that point "mail.company.com" to "xyz.company.com"? I'd sooner not have to have users needing to know the specific name of the server they need (whether using pop3/imap/smtp/webmail) but I don't know if using an alias is likely to cause problems? TIA, Paul On Fri, 27 Jan 2006 02:56:43 -0800, Paul Hutchings <paul@spamcop.net> wrote: >Cana anyone tell me if there are any implicat...

how to jump to the next column with "enter" in excel?
Hi, The "tab" key is not an option? "dragos" <dragos@discussions.microsoft.com> wrote in message news:076B9F2B-AE6B-4DCD-96ED-6B5AAE35DC4B@microsoft.com... > ...

Email naming standards
I'd like to take a survey of email address naming standards and which ones you use. I've looking online for a "naming conventions" FAQ but had very little luck. At my company, it's currently lastnamefirstinitial@company.com, or smithj@company.com. I would like to lobby to change it simply because it's so out of step with the rest of the world. The way I see it, when it's jsmith@company.com there's a logical progression from left to right. We are changing the company name (and domain name), so the way I see it this is the perfect time to do it. Opinions? ...

automatic capitalize first letter of payee names
I just transferred from Quicken. I had it set to automatically capitalize payee names as I typed them. Is there a way to do it in Money 2004? No. "GB" <garyburkhardt@centurybt.com> wrote in message news:OZT4Yz1jDHA.3256@tk2msftngp13.phx.gbl... > I just transferred from Quicken. I had it set to automatically capitalize > payee names as I typed them. Is there a way to do it in Money 2004? > > Type them correctly once. The next time you start to type, the field will autofill. When you tab to the next field cap and all will be as when you originally typed the...

Field Chooser #2
Guys-is it possible that some of the fields in Outlook 2000 (from Field Chooser) can simply disappear or could the problem be that somebody else is accessing my mailbox? It happened to me that the From field and Subject field have simply disappered twice without my action? Could it be a bug or something similar? Is it possible those fields are already in the table view? I've been spooked by that one before. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

authentication #2
I seem to keep getting a password and ID prompt in outlook for my HTTP Hotmail account even though I am entering the correct password and ID because i can get to this account over the internet. remember my password is also checked in tools\account\properties etc. Version of Outlook? Does it work if you log into the site in a browser? -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Exchange Messaging Outlook ...

MsgBox #2
I have been search the web for hours looking for example spreadsheets using MsgBox to no avail. Can someone direct me in the right direction? What I am looking to do is ask a question and if the answer is "yes" do 1 thing and if "no" do something else. "Ed Davis" <ed.davis1@verizon.net> wrote in message news:4EBF4D2F-7C3F-4BAB-8366-A1C744276744@microsoft.com... >I have been search the web for hours looking for example spreadsheets using >MsgBox to no avail. > Can someone direct me in the right direction? > Hi Look at this: Sub test()...