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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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)
 
Upvote 0
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>
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,503
Members
449,944
Latest member
parag385

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