Removing the final comma and any text after it

Jon_London

New Member
Joined
Jan 24, 2019
Messages
12
Office Version
  1. 365
Hi everyone

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

1647368532518.png


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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,545
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=LEFT(A2,FIND("^",SUBSTITUTE(A2,",","^",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

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

Jon_London

New Member
Joined
Jan 24, 2019
Messages
12
Office Version
  1. 365
Fantastic, thank you. Both of those work perfectly.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,545
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,175,597
Messages
5,898,372
Members
434,708
Latest member
matthewdrhodes

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