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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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