Removing Spaces from Beginning & End of a Cell

[aelingil]

At the moment, I have Cells in a workbook that have "_" in the beginning, or end, or both. I have two commands to remove the _ in one or the other, but I cannot get it to do both in one sitting. I really just need to remove "_" from the beginning and end of the cell. If what i'm doing will not work, please let me know if there is a way.

A1: _Direct
B1: Direct_
C1: _Direct_

The Two Commands i have are below.
=IF(RIGHT(A2)="_",RIGHT(A2,LEN(A2)-1),"")
=IF(LEFT(A2)="_",LEFT(A2,LEN(A2)-1),"")

These work for A1 & B1, however C1 will only do one or the other. I tried adding both If Statements together, however what i got was "Direct__Direct".. which did not really do what i wanted.

The word "Direct" will change to show an Agency we have in our list.
Example:
"_Jon_Doe_Agency"
"Doe_Agency_"
"_John_INS_"

[FDibbins]

Hi and welcome to the forum

Try this instead...
=SUBSTITUTE(A2,"_","")

[martindwilson]

are there any in the middle?

[aelingil]

Yes, there are.. I didnt notice it was taking all of them out.

I added a xls showing that it is 'started' and 'finished' should be.

Database Error.xls

[martindwilson]

=substitute(substitute("__"&a2&"__","___",""),"__","")

[aelingil]

That looks like it will do it

Thank you both for your help!

[FDibbins]

Happy to help