I want to remove the first and last characters of a text in a cell. I know how to remove just the first or the last with formulas such as =LEFT(A27, LEN(A27)-1) but i want to combine two formulas at the same time for first and last character or maybe there is a formula that I'm not aware of which removes both (first and last characters) at the same time.

I know about Power Tool but i want to avoid using this tool and I'm trying to realize this simply by formulas.

CodePudding user response：

You could use the REGEXREPLACE() function:

=REGEXREPLACE(A27, "^.|.$", "")

The regular expression used here matches:

• ^. the first character after the start of the string
• | OR
• .$ the last character of the string

CodePudding user response：

better not to use this but it works too:

=RIGHT(LEFT(A27, LEN(A27)-1), LEN(LEFT(A27, LEN(A27)-1))-1)

=LAMBDA(x, RIGHT(LEFT(A27, x), LEN(LEFT(A27, x))-1))(LEN(A27)-1)

=LAMBDA(x, LAMBDA(y, RIGHT(y, LEN(y)-1))(LEFT(A27, x)))(LEN(A27)-1)

=LAMBDA(x, RIGHT(x, LEN(x)-1))(LEFT(A27, LEN(A27)-1))