CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
John Grischam
John Grischam

Reputation: 263

Remove characters in Googlesheets

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.

Upvotes: 2

Views: 17899

Answers (2)

player0
player0

Reputation: 1

better not to use this but it works too:

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

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

Upvotes: 5

Related Questions