left to first or second comma

[terrysoper1973]

I was wondering if there is a way to do a logical operation like this.

If a cell containg text has 2 comma delimiters, extract everything from the left of the second comma.

or if the cell has on comma, extract everything to the left of the first comma.

Any takers

Thanks

Dennis

Thanks For your help everyone

[bentleybob]

Please Login or Register  to view this content.

This ony looks for zero, one, or two commas ...

[bentleybob]

Sorry. Re-read your post. You say if it has on[e] comma, extract everything to the left of that comma. But if it has two commas, do you really want everythng FROM the second comma, or up to it? I did the latter, but it's easy to change if I know what the rule is.

[NBVC]

Try:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1,",","^^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1)

this extracts from first char to just left of last comma.

[terrysoper1973]

It's somthing like this.

2737 bakers street dufferin, Ontario
2737, bakers street dufferin, ontario

Where I want everything but ontario

Thanks

Terry

[bentleybob]

Then either solution works (although NBVCs does not address when there's no comma).