0

So, I have a bunch of cells like this:

Freddie’s Restaurant 102 Super Lane San Diego California

and I want to pull out the name, which is always preceding the start of the address (102 Super...). Is there any way I can do this? Thanks.

  • Is that always two words in all cases like "Freddie’s Restaurant" ? – Prasanna Oct 27 '14 at 14:44
  • Nope, unfortunately not. It will be a different name with different combinations of words – user1282637 Oct 27 '14 at 14:46
3

With data in A1 use:

=TRIM(MID(A1,1,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1)))-1))

EDIT#1:

To get the "rest of the data" use:

=TRIM(MID(A1,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1))),9999))
| improve this answer | |
  • Is there any way I can then get the rest of the cell into another? For this example, I need 102 Super Lane San Diego California Any ideas? Thanks – user1282637 Oct 27 '14 at 16:19
  • @user1282637 Yes.........see my EDIT#1 – Gary's Student Oct 27 '14 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.