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:
[TABLE="width: 812"]
<tbody>[TR]
[TD]
Actual
[/TD]
[TD]Output Required
[/TD]
[/TR]
[TR]
[TD]Assoc Prof.
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc Prof.
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD] Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD] Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD] Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc Prof.,
[/TD]
[TD]Assoc Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Dean & Prof.
[/TD]
[TD]Assoc, Dean & Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Dean & Prof.
[/TD]
[TD]Assoc, Dean & Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Dean, Prof., and Dir.,
[/TD]
[TD]Assoc, Dean, Prof., and Dir.
[/TD]
[/TR]
[TR]
[TD]Assoc, Dir., Jt. Degree Applied Legal Theory & Analysis Prog.,
[/TD]
[TD]Assoc, Dir., Jt. Degree Applied Legal Theory & Analysis Prog.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof North- western
[/TD]
[TD]Assoc, Prof North- western
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof. & Dir.,
[/TD]
[TD]Assoc, Prof. & Dir.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof. & Dir.,
[/TD]
[TD]Assoc, Prof. & Dir.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof.
[/TD]
[TD]Assoc, Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof.
[/TD]
[TD]Assoc, Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof.
[/TD]
[TD]Assoc, Prof.
[/TD]
[/TR]
[TR]
[TD]Assoc, Prof.
[/TD]
[TD]Assoc, Prof.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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".