LinkBack URL
About LinkBacks
This is in one column. I would like to be able to separate the city state and zip to their own columns. Below is an example of what comes into one column
example
4768 LOGAN AVE A | SAN DIEGO 921133759
This is in one column. I would like to be able to separate the city state and zip to their own columns. Below is an example of what comes into one column
example
4768 LOGAN AVE A | SAN DIEGO 921133759
you could use text to column.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
You will need to provide a few more samples of what you have and what you want.
In many cases, this a really hard to do, because a street name can have 1 or more words, as can the state - how would you determine if the 2nd (or 3rd) word was part of the street or state name?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
OP, your example doesn't even have a state listed.Like FDibbins said, we'll need a better example (workbook preferable) that shows exactly what you are dealing with, as there are multiple methods and knowing which is best requires looking at the exact situation you are facing.
Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!
The first row has an example of what I would like to accomplish
Enter in B1 and fill down
Formula:
Please Login or Register to view this content.
Enter in D1 and fill down
Formula:Please Login or Register to view this content.
Enter in C1 and fill down
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
address: =LEFT(A2,FIND("|",A2)-2)
city: =MID(A2,FIND("|",A2)+2,LEN(A2)-(FIND("|",A2)+2)-9)
zip: =RIGHT(A2,9)
Ben Van Johnson
Another way if you can tolerate small mega-formula. In B2 filled across to column D and filled down.
=IFERROR(TRIM(MID(SUBSTITUTE(REPLACE($A2,LOOKUP(25^25,FIND(" ",$A2,ROW($1:$50))),1,REPT(" ",99))," | ",REPT(" ",99)),(COLUMNS($A:A)-1)*99+1,99)),"")
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks