i have been looking for a way to put a space between the street number and address and then move the street number and address to seaparate colums and i come up with nothing pertaining to my situation. i need some serious help.
i attached the excel sheet with a few address from a much larger workbook of 4300 addresses. as you see i am working on a big project, so i would greatly appreciate some feedback
Last edited by iwanttoplaywii; 03-24-2011 at 07:38 PM. Reason: its solved
What's the logic here, what are the rules?
EDIT: this should separate the number and the rest:
Please Login or Register to view this content.
Last edited by StephenR; 03-24-2011 at 05:46 PM.
the logic is that i am a city worker and i have a HUGE workbook based on a water softener survey that has peoples addresses so i do not want to post all of the address and my boss wants me to categorize by street names and i cant do that until i get the street names and address separated.
p.s. when i run the code i get invalid procedure call or argument
You could probably use the Text to Columns feature.
http://office.microsoft.com/en-us/ex...010102340.aspx
EDIT: Just looked at your file and saw no spaces between numbers and names so never mind T 2 C
Last edited by Cutter; 03-24-2011 at 06:02 PM.
that only works if the text is already spaced. my street number and address are combined alltogether like this 155hawkct not like 155 hawk ct.
Yep - already edited my post.
My logic is that I am a busy person who was willing to give up a little bit of my time for free to help someone, but I'm not going to do that for someone as ill-mannered and ungrateful as you.
i was not being " ill-mannered and ungrateful" i was simply saying that i wanted to protect the individuals addresses and that i cannot categorize the addresses until my question was answered. that's all i was saying. i had no inention to be ungrateful towards anyone.
Well I'm sorry but your post reeked of an attitude that your time is so much more valuable than anyone else's. Perhaps I read too much into it. However, if you can't explain how an address should be split up,then how on earth is anyone else supposed to know? You have to help people here to help you. We're not psychic, we've never seen your data before, we know nothing about it. We only know what you choose to tell us.
i totally understand what you are saying and that is why i rarely use forums becuase i can never tell if my postings are seen as being rude. i hope we on the same page now.
i will try to restate my question differently:
i have addresses that have the street number and street name put together, meaning there is no space in between the number and the name. i would like to know if there is any way possible to separate the street number and name and then put the separated street name and number into different colums without manually going through 4300 address and putting a space between the street number and name myself.the reason the street number and name are put together like that is that i used a scanner to scan the barcode off of the survey's and the scanner did not separate the street name and number for me. the attachment i post was just a snippet of the complete address list that i own.
Last edited by iwanttoplaywii; 03-24-2011 at 06:35 PM.
To put a space between the first numbers and the rest:
I had this code by mdmackillop laying around somewhere.Please Login or Register to view this content.
I would not be able to make changes for you though. (I would but the code would not work after)
John
THANKYOU jolivanes SO MUCH!! your code worked
Yes, I understood what you were asking in your original post. I think you'll find the code above will work if you remove the leading spaces in the first two entries.
I was trying to send a this before it was solve but anyways here it goes. this will separete the first numbers from everything else is an array so make sure to hit CTRL SHIFT ENTER.
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))&" "&MID(A1,MATCH(FALSE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),LEN(A1))
If you add the above to StephenR's code you'll be better off as it enters the nummers and addresses in the next columns over.Please Login or Register to view this content.
It has the added advantage that StephenR knows what he's doing while I just copied code I had around somewhere.
John
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks