Hi:
I'm trying to clean addresses. Very occassionally, the address is written #123 Main St in the primary address field. The vast majority of the time, the address field is 123 Main st. Sometimes, the address field contains secondary address information too such as 123 Main st # 456. For the third case, I remove the # 456 from the field to a new field by finding and replacing then using text to columns. For instance, find space#space (space = one press of space bar) repalce with ,#space.Then using text to cloumns, I make delimitor , and click OKAY. Problem is, if there is a #123 Main st, the entire entry would get moved to the second column, which would leave this primary address field NULL. So, I need a function to run first that removes only the # and the space immediately after it (if there is one) from the left most position in the string before separating the #456 type of information from the right end of the string.
Anyone have a solution?