0

This formula works on some of of my cells and not others, I've checked to see if the formatting was different but could find nothing.

I have for instance

a Cell that says "Dell Computers 12150 sandy dr portland or 97229"

What I want is just to get "Dell Computers" by itself.

I've tried a few things, the most obvious for mew as to:

=LEFT(A1,FIND({1,2,3,4,5,6,7,8,9,0},A1,1)-2)

This works for some, for others it doesn't (for some for instance the above if there are two 1's in the address it gives me "Dell Computers 1", taking the second 1 as being the value)

Maybe a newb question, but what am I doing?

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
Dm3k1
  • 187
  • 3
  • 8
  • 21
  • the example `"Dell Computers 12150 sandy dr portland or 97229"` and the formula `=LEFT(A1,FIND({1,2,3,4,5,6,7,8,9,0},A1,1)-2)` yields the perfect result in my pc i.e. `Dell Computers`. I tried with other combinations as well, the result is all fine there as well – Youbaraj Sharma Dec 09 '13 at 17:48
  • try changing the array to {2,1,3,4...} - the issue would be it is started with a 2 originally it would return Dell computers 1 - even though I want it to just find the first number it comes to, it would firt search for a 2, then a 1, then a 3. I'm trying to avoid having to do a min(find.... etc for all 10 digits. – Dm3k1 Dec 09 '13 at 17:57
  • What works is `=left(A1,MIN(IFERROR(FIND(1,A1,1),10000),IFERROR(FIND(2,A1,1),10000)......` but there has to be a more professional way to do this. It is essentially looking for each individual number, taking the smallest length value and if the number doesnt exist assign it a 10k (so it will not be the min) a little much for this simple task. – Dm3k1 Dec 09 '13 at 18:10
  • Are you OK with using a custom function or does have to be a formula? – alpha bravo Dec 09 '13 at 18:23
  • In this case, because I am showing it to someone who I don't expect to be in that comfort zone i was looking for a formula. but if you have a custom function I would love to take a look. – Dm3k1 Dec 09 '13 at 18:46

2 Answers2

1

use this function and set cell value to 

=udfRegEx(A1,"^\D+")
Community
  • 1
  • 1
alpha bravo
  • 7,838
  • 1
  • 19
  • 23
1

Also, do take a look at this old thread. I haven't quite parsed how that formula works; but this formula 

=LEFT(A2, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, A2 & "1234567890"))-1)

Works for

  • Dell Computers 12150 sandy dr portland or 97229
  • Dell Computers 250 sandy dr portland or 97229
  • Dell Computers sandy dr portland or 97229
Community
  • 1
  • 1
Chris
  • 868
  • 7
  • 6
  • I was in the process of posting the same link... The appended "1234567890" prevents the `MIN()` function from throwing an error if one or more of the digits in the seach array `{1,2,3,...}` was not found. If all digits are found in the string being searched, then `Min()` can do its job of finding the earliest located position. – Tim Williams Dec 09 '13 at 19:17