如何用Excel公式去除字符串首尾特定字符？以去除'-'为例

Great question! Let's break this down clearly:

First, let's clarify: the basic, standard TRIM function (like Excel's default TRIM(), or older SQL implementations) only removes whitespace from the start and end of a string, plus extra spaces in the middle. It can't directly target - characters.

But here's the good news: many modern tools, databases, and programming languages have extended TRIM functionality that lets you specify which characters to trim. In those cases, yes, you absolutely can use a TRIM-like function to solve your problem.

Examples of how this works in common environments: #

• Python: Use the built-in str.strip('-') method, which acts as a TRIM variant for custom characters:

  "-Some-thing-".strip('-')  # Returns "Some-thing"
  "-Another".strip('-')      # Returns "Another"
  "One more-".strip('-')     # Returns "One more"
  

• MySQL/PostgreSQL/SQL Server 2017+: These databases support specifying the trim character directly in the TRIM function:

  -- MySQL/PostgreSQL
  SELECT TRIM(BOTH '-' FROM '-Some-thing-') AS trimmed_str;
  -- SQL Server
  SELECT TRIM('-' FROM '-Some-thing-') AS trimmed_str;
  

  Both will return Some-thing for your first example.
• Older Excel versions (pre-365): Since the default TRIM() only handles spaces, you'll need a combination of functions to target - specifically. For example, if your string is in cell A1:

  =MID(A1, IF(LEFT(A1)="-", 2, 1), LEN(A1) - IF(LEFT(A1)="-", 1, 0) - IF(RIGHT(A1)="-", 1, 0))
  

  This checks if the first/last character is -, adjusts the start position and length accordingly to snip those off, while leaving middle - intact.
• Excel 365/2021: You can use the TEXTBEFORE and TEXTAFTER functions together for a cleaner approach:

  =TEXTAFTER(TEXTBEFORE(A1&"-", "-", -1), "-", 1)
  

To sum up: #

• If your tool/language supports custom-character TRIM (most modern ones do), then yes, TRIM (or its variant) can perfectly handle your requirement.
• If you're stuck with a basic TRIM that only handles whitespace, you'll need to use alternative function combinations to target the - characters at the start/end.

内容的提问来源于stack exchange，提问作者cooper_milton