Copy all info before a specific character, when there are multiples of the same character

Keeley

New Member
Joined
Jun 3, 2015
Messages
3
Hi there,

I hope someone can help me - I am trying to copy all the information in a cell before the last ",". I have a number of cells which contain info such as:

abc,def,ghi
ab,cdef,ghij
abcde,fgh

There is no set number of commas in all the cells and the lengths are all different. I want to be able to copy everything before the last comma.

Hope you can help!
Thanks.
K
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG03Jun26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Offset(, 1) = Mid(Dn.Value, 1, InStrRev(Dn.Value, ",") - 1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

Assuming you first string in A2, pit this in a cell and drag down.


=LEFT(A2,FIND(CHAR(7),SUBSTITUTE(A2,",",CHAR(7),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)
 

Keeley

New Member
Joined
Jun 3, 2015
Messages
3
Hi,

Assuming you first string in A2, pit this in a cell and drag down.


=LEFT(A2,FIND(CHAR(7),SUBSTITUTE(A2,",",CHAR(7),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)[/QUOTE

Thanks Mike but that doesn't seem to be working it returns #VALUE! for me

Here are some examples of the cells I'm working with

Oberhäuser Brücke, Riesling, Spätlese, Dönnhoff

Wehlener Sonnenuhr, Riesling, Auslese, JJ Prüm

and I everything before the final ","

thanks!!!

<colgroup><col></colgroup><tbody>
</tbody>
 

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

For me the formula works fine with those 2 strings you posted. The only time the formula will return a #VALUE! error for me is if there are no commas in the cell.

Can you upload a copy of your workbook to Onedrive or some other file sharing site and post the link?

We can fix the error where there are no commas like this.


=IFERROR(LEFT(A2,FIND(CHAR(7),SUBSTITUTE(A2,",",CHAR(7),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1),A2)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,158
Messages
5,835,718
Members
430,383
Latest member
Kastore

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
Top