Formula-delete data after comma

[arnm]

Excel 2007
1. I want to delete data after comma. I am good when the data has a comma (1st and 3rd ex).
2. I get an error (#VALUE!) when the data does not have a comma. (2nd ex.).

formula in Col.K =LEFT(D6,FIND(",",D6,1)-1)
I tried using the IF formula with it, but I still get the error due to the FIND. I also know I can use the Text to Col. but we wanted a formula since we would be refreshing this report.


Data in col. D
Regina, SK Canada
Canada
Edmonton, AB Canada

Want it to look like col k:
Regina
Canada
Edmonton

Any help is greatly appreciated!!

 

[Gerald Higgins]

Perhaps

=if(iserror(find(",",d6,1)),d6,left(d6,find(",",d6,1)-1))

 

[arnm]

Gerald Higgins

Wow!! you just took over!! Thanks!!!

 

[Gerald Higgins]

you just took over!!

Is that good ?

Thanks for coming back to say thanks !!!

 

[mikerickson]

By using FIND on D6&"," the ISERROR can be eliminated.

=LEFT(D6,FIND(",", D6&"," ,1)-1)

 

[Gerald Higgins]

Neat, Mike. One to remember.

 

[Peter_SSs]

... and you don't really need that first '1' since that is the default starting position for FIND. So this should do:
=LEFT(D6,FIND(",", D6&",")-1)

 

[Greg Truby]

Firstly, let me go on record as voting for Peter's polished up version of Mike's formula:

• =LEFT(D6,FIND(",", D6&",")-1)

Having said that, I'm havin' a hard time believin' that given that arnm actually told us he was using 2007, nobody made use of the new IFERROR(), i.e.

• =IFERROR(LEFT(D6,FIND(",",D6)-1),D6)