Formula-delete data after comma

A

arnm

New Member
Joined
Jul 15, 2005
Messages
8
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!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
... 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)
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
HOW TO CONVERT 2ND COMMA IN A DOLLAR AMOUNT TO A DECIMAL
2
Replies
14
Views
876
JuicyMusic
J
I
  • Question Question
Delete row on another sheet from a userform
2 3
Replies
22
Views
650
ipbr21054
I
J
  • Solved
Sorting occurrences of Postal Code in column / Sorting by occurrence high to low
Replies
7
Views
369
Fluff
Fluff
S
  • Solved
Delete rows from another tab based on data in cells and keep formula
Replies
1
Views
440
rpaulson
rpaulson
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
1K
MissDB101
M

Forum statistics

Threads
1,222,665
Messages
6,167,491
Members
452,113
Latest member
GunsofBrixton

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top