Removing the final comma and any text after it

[Jon_London]

Hi everyone

I have a requirement to remove the final comma and any text after it in a spreadsheet (example image below):

So, in the example I would like to remove ,SomethingOrOther on the second row then ,ThisAndThat on the third row and so on. The text after the final comma can vary quite a lot, so a simple replace wouldn't really work.

Does anyone know a way to do this please? We have a huge spreadsheet and have to manually remove this data each month.

Many thanks
Jon

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

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

 

[jtakw]

Hi,

Assuming data start at A2 down, in B2, copied down:

=TRIM(LEFT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2)),6),LEN(A2)))

 

[Jon_London]

Fantastic, thank you. Both of those work perfectly.

I've also updated my version of Excel in the Account section.

 

[Fluff]

Glad we could help & thanks for the feedback.