Formula required to clean simple formatting errors

[manoj18071965]

I have a long list in column A in excel. I am seeking for formula to clear below formatting errors:

1. Remove Extra space or spaces appearing before text starts in a cell
2. Remove Extra space or spaces appearing after text starts in a cell
3. Remove comma if appearing at the end of text in a cell

Below is the example of output required:

Actual	Output Required
Assoc Prof.	Assoc Prof.
Assoc Prof.	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc Prof.,	Assoc Prof.
Assoc, Dean & Prof.	Assoc, Dean & Prof.
Assoc, Dean & Prof.	Assoc, Dean & Prof.
Assoc, Dean, Prof., and Dir.,	Assoc, Dean, Prof., and Dir.
Assoc, Dir., Jt. Degree Applied Legal Theory & Analysis Prog.,	Assoc, Dir., Jt. Degree Applied Legal Theory & Analysis Prog.
Assoc, Prof North- western	Assoc, Prof North- western
Assoc, Prof. & Dir.,	Assoc, Prof. & Dir.
Assoc, Prof. & Dir.,	Assoc, Prof. & Dir.
Assoc, Prof.	Assoc, Prof.
Assoc, Prof.	Assoc, Prof.
Assoc, Prof.	Assoc, Prof.
Assoc, Prof.	Assoc, Prof.

<tbody>
</tbody>

 

[MUKESHY12390]

Here you go... copy and paste entire formula to cell and make sure Column "A" has data.

=IF(TRIM(RIGHT(A2))=",",MID(CLEAN(TRIM(A2)),1,LEN(CLEAN(TRIM(A2)))-1),CLEAN(TRIM(A2)))

 

[mikerickson]

Perhaps
=TRIM(REPLACE(TRIM(A1), LEN(TRIM(A1)), 1, SUBSTITUTE(RIGHT(A1),",","")))

 

[Peter_SSs]

Here you go... copy and paste entire formula to cell and make sure Column "A" has data.

=IF(TRIM(RIGHT(A2))=",",MID(CLEAN(TRIM(A2)),1,LEN(CLEAN(TRIM(A2)))-1),CLEAN(TRIM(A2)))

That does not seem to work with data like "Assoc, Prof. & Dir.,  " (ie comma then space(s) at the end)

Perhaps
=TRIM(REPLACE(TRIM(A1), LEN(TRIM(A1)), 1, SUBSTITUTE(RIGHT(A1),",","")))

That does not seem to work with data like "Assoc, Prof. " (ie space(s) at the end with no comma immediately before)


My suggestion:
=LEFT(TRIM(A2),LEN(TRIM(A2))-1)&SUBSTITUTE(RIGHT(TRIM(A2)),",","")

 

[mikerickson]

Thanks for the catch Peter_SSs

 

[MUKESHY12390]

yeah, thats the perfect answer.

I got confused with this line "Remove comma if appearing at the end of text in a cell".