咨询：在SharePoint计算列中查找字符最后一次出现位置并提取后续内容适配Power Automate

Got it, let's solve this problem where you need to pull the segment after the last underscore from your strings—whether it's pure numbers or numbers with letters. Here's a reliable formula that works for all your test cases and plays nicely with Power Automate:

=RIGHT([YourColumnName], LEN([YourColumnName]) - FIND("~", SUBSTITUTE([YourColumnName], "_", "~", LEN([YourColumnName]) - LEN(SUBSTITUTE([YourColumnName], "_", "")))))

How This Formula Works (Step-by-Step Breakdown) #

Let's break it down so you understand exactly what's going on:

1. Count total underscores: LEN([YourColumnName]) - LEN(SUBSTITUTE([YourColumnName], "_", "")) calculates how many underscores exist in your string. For DSN_KANSAS_727, this returns 2.
2. Replace the last underscore: SUBSTITUTE([YourColumnName], "_", "~", ...) swaps the final underscore with a unique character (I used ~—just make sure this character never appears in your actual data). So DSN_KANSAS_727 becomes DSN_KANSAS~727.
3. Locate the swapped character: FIND("~", ...) gives the position of that ~, which marks exactly where the last underscore was.
4. Extract the target substring: LEN(...) - FIND(...) calculates the length of everything after that position, then RIGHT() pulls that final segment.

Test Case Validation #

This formula works perfectly for all your examples:

• Input: DSN_KANSAS_727 → Output: 727
• Input: DSN_INNOVATION_335 → Output: 335
• Input: DSN_KANSAS_727B → Output: 727B
• Input: PCBA_COLUMBIA_16C → Output: 16C

Power Automate Compatibility Tips #

To avoid issues when using this calculated column in your email Flow:

• Set column type to "Single line of text": Don't use number type—your results include letters (like 727B), which will trigger errors. Text type ensures Power Automate reads the value correctly without conversion hiccups.
• Account for refresh delay: SharePoint might take a few seconds to update the calculated column after the source column changes. Add a short Wait action (5-10 seconds) in your Flow before pulling the calculated column value, or set the trigger to fire when the calculated column is modified.
• Use dynamic content directly: When building your email, just select the calculated column from the dynamic content list—no extra expressions are needed to parse it.

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