Removing the last comma from a cell - Formula Also Removes the Preceding Zero

[Theemeadelis]

I have a data set where I need to remove the last comma that is appearing in a lot of the cells. As there is often a space after the last comma too, I have been using the below formula:

=LEFT(A1,LEN(TRIM(A1))-(RIGHT(TRIM(A1))=","))

Unfortunately I have found that if a cell has a zero before the last comma, the above formula is also removing the zero as well - please see the below example:

Does anyone know why this is happening, and how I can get the formula to work without also removing the zero that precedes the last comma?

 

[keda duck]

can you provide this data?

 

[JEC]

Nothing wrong with your formula. Works fine here.

=TEXTBEFORE(A1&"^",",^",,,,A1)

 

[Peter_SSs]

Does anyone know why this is happening

From looking at you image I suspect it is because there is a double space after the ":"

and how I can get the formula to work without also removing the zero that precedes the last comma?

My TEXTBEFORE is marginally different

23 07 16.xlsm
	H	I
1
2	APT: N030	APT: N030
3	APT: N090, N120,	APT: N090, N120
4	APT: N090, N120,	APT: N090, N120
Last Comma

Cell Formulas
Range	Formula
I2:I4	I2	=TEXTBEFORE(H2,",",-1,,,H2)

@JEC
I think that you may have overlooked this:

there is often a space after the last comma too,

 

[JEC]

Yes I did