0

I have a column of data, that I need to separate into two columns. I've tried using the methods outlined here, as well as using Flash Fill, but neither seems to get things just right.

Examples of entries in the column would be:

Main Street 12
North Main Street 3
South Main Street 4A
Main Street B
10th Main Street 12A

The last example above in particular is what caused the issue with the first method cited, as the numbers at the beginning of the entry result in additional information being moved in the second column when separating.

What I need is a method of creating two columns based off of the existing entry. Column B would contain the final block of string; while column A would contain everything else.

Happy to use Array Formulas; haven't really used VBA or Macros before but will try if I need to.

Any ideas?

tigeravatar
  • 26,199
  • 5
  • 30
  • 38
FMac
  • 5
  • 2
  • If you're looking to just get the last space in the string and move over everything to the right of it (so `12`, `3`, `4A`, `B`, and `12A`, then the last part of the accepted answer here should work for you: https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string – tigeravatar May 23 '17 at 14:40

1 Answers1

0

If you are fine with 2 formulas, then give a try with this,

enter image description here

Suppose your data is in Column A, enter the below formula in Column C first,

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",999)),999))

then, formula for Column B,

=LEFT(A1,FIND(C1,A1)-2)

Column B uses values from column C and A, so essentially C first and then would be B.

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27