ACCESS QUERY SPLIT DATA BASED ON CRITERIA
Posted on 2014-03-20
I have a table in access with address information which the column contains a special character "|" to show where I want to split the data. There could be from 1 to 4 of these special characters "|" or none. I want to put each instance in it's own column. All the help is appreciated. Thanks!
» If the first instance of | appears to take anything to the right of this character
» If the 2nd instance of | appears to take anything to the right but if the 3rd instance appears to take anything between the 2nd instance and 3rd instance.
» If the 3rd instance of | appears to take anything to the right as well but if the 4th instance appears to take anything between the 3rd instance and 4th instance.
Below is example 1:
MC Cap Adv, LLC|Financial Life Building|4234 Brownsville Rd, Suite 129
Below is example 2:
Investment Research & Advisory|Pacific Center Plaza|80 West Peach Street|Suite 200B