How to split column text to another column?

Split text out of column

• I need to prepare a large document for import. One of the fields has address and city both in it. Only spaces separate the components. I need to be able to separate the city from the address portion. I have tried various formulas however, they don't solve the problem. One gets all the 1cities to another column, but then i can't find something to delete the city text from the original column. And if i delete it manually, the the column i created disappears. Here is sample data that resides all in 1 column. Spaces are the only separators. The only commonality is 85% of the data is 1and on the right side of the last space. 1234 Boardwalk Blvd Dallas 2323 Park Ln San Antonio If i can pull the lastinto a separate column and delete it from the original column, that would be really great. Doing something like the San Antonio one would be a nice have but can be accomplished manually if need be. Frank

• Answer:

  Yes, the cell containing the full address cannot be edited via a formula since a cell formula cannot change any cell other than the cell that contains the formula. Of course this can easily be accomplished with a macro, but I suspect you would prefer a cell formula-based solution. Unfortunately, to do this withworksheet function yields a very unwieldy solution as the built-in text functions are not very complete. I suggest using the following user-defined functions: Function LastWord(strIn As String) As String 'returns the lastin the input string strIn Dim Rstr As String Dim iCh As Integer Rstr = StrReverse(strIn) iCh = InStr(1, Rstr, " ") LastWord = StrReverse(Left(Rstr, iCh - 1)) End Function Function StripLastWord(strIn As String) As String 'returns the lastin the input string strIn Dim Rstr As String Dim iCh As Integer Rstr = StrReverse(strIn) iCh = InStr(1, Rstr, " ") StripLastWord = StrReverse(Right(Rstr, Len(strIn) - iCh)) End Function The first will yield the lastin a string. The second yields the entire string with the lastremoved. To install these functions in your workbook simply go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and past this code in that window. A bit more information that I did not supply before because my laptop battery was giving up the ghost: Say for example your full address string "1234 Boardwalk Blvd Dallas" is in cell B1. You can get the city (the last word) by using the formula =LastWord(B1) (should yield "Dallas") and the street address by using the formula =StripLastWord(B1) (should yield 1234 Boardwalk Blvd) These UDFs could be made "smarter" to recognize city (e.g., Los Angeles) by coding in common city prefixes (Los, Las, El, La, San, Del, Rio, St., New, etc.) but this still would not cover all possible two and threecity names. If you would like the additional code to do the common prefixes, let me know. If after using these functions you want to replace the full address, simply copy and paste special the cells containing the formulas as values, then delete the column containing the full address. If you do prefer a macro based solution, feel free to follow up and I will provide it.