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

[Keeley]

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

 

[MickG]

Try this:-

[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]

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]

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]

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)

 

[Keeley]

Amazing, that worked great. Thank you.