+ Reply to Thread
Results 1 to 5 of 5

Excel 2016 Custom String Trim in a Cell

Hybrid View

  1. #1
    Registered User
    Join Date
    03-21-2022
    Location
    Las Vegas
    MS-Off Ver
    Office 2016
    Posts
    2

    Excel 2016 Custom String Trim in a Cell

    I hope someone is able to assist with this Excel formula. I need a custom Trim performed in a column. I need everything before the last occurrence of the word "Patch" plus the following character Trimmed out. For example the final result should be "PatchJ" or "PatchM" etc... NOTE The last occurrence of the word Patch is not always directly at the end of the string. Example images of this variances are attached (Highlighting in yellow the word I need to keep)...
    Here is a sample of two variances to be copied from:

    Need to keep "PatchJ" (It IS acceptable to keep the previous four numbers as well "1326PatchJ"

    1511 13_26/core 13_26/system 13_26/3rdParty 13_26/NgVtuCfg/FixedSimmons 13_26/NMDTIni/FixedRoute 13_26/NMotorolaSB9600RadioDriverIni/Reband 13_26/NRfCommsSubsystemIni/FixedSimmons 13_26/NWirelessLANSubsystemIni/Fixed 13_26/OdoConfigIni/NewFlyer670 13_26/systemIni/Fixed 13_26/Logging/std 13_26/RadioConfigIni/Reband/Pri2Sec1 13_26/IRQ_Init_TableDat/DCM 13_26/1326PatchA/NMotorolaSB9600RadioDriverIni/Reband 13_26/1326PatchA/DR 13_26/1326PatchA/1326PatchA 13_26/1326PatchA/NMDTIni/FixedRoute 13_26/1326PatchA/RadioConfigIni/Reband/Fallback18/Pri3Sec1 13_26/1326PatchB/1326PatchB 13_26/1326PatchD/1326PatchD 13_26/1326PatchC/1326PatchC 13_26/1326PatchC/NMDTIni/FixedRoute 13_26/1326PatchE/1326PatchE 13_26/1326PatchE/DR 13_26/1326PatchE/RadioConfigIni/Reband/Fallback18/Pri2Sec3 13_26/1326PatchF/1326PatchF 13_26/1326PatchF/NFareboxSubsystemIni/Build13 13_26/1326PatchF/systemIni/Fixed 13_26/1326PatchH/1326PatchH 13_26/1326PatchI/1326PatchI 13_26/1326PatchJ/1326PatchJ

    and

    Need to keep "PatchP" (It IS acceptable to keep the previous four numbers as well "1603PatchP"

    166141 16_03/core 16_03/system 16_03/3rdParty 16_03/NgVtuCfg/FixedSimmons 16_03/NMotorolaSB9600RadioDriverIni/Reband 16_03/NRfCommsSubsystemIni/FixedSimmons 16_03/NWirelessLANSubsystemIni/Build16/Fixed 16_03/OdoConfigIni/NewFlyer670 16_03/systemIni/Build16/Fixed 16_03/Logging/on 16_03/RadioConfigIni/Common 16_03/amdtStart/NoExtSignDownload 16_03/NVehicleMonitoringSubsystemIni/Build16 16_03/1603PatchA/1603PatchA 16_03/1603PatchA/NMotorolaSB9600RadioDriverIni/Reband 16_03/1603PatchA/RadioConfigIni/Reband/Pri1Sec3 16_03/1603PatchB/drconfigIni/Build16 16_03/1603PatchB/1603PatchB 16_03/1603PatchC/1603PatchC 16_03/1603PatchC/RadioConfigIni/Reband/Fallback17/Pri1Sec3 16_03/1603PatchD/1603PatchD 16_03/1603PatchE/1603PatchE 16_03/1603PatchE/FunctionalHwConfigIni/BikeRequest 16_03/1603PatchE/NAnnunciatorSubsystemIni/BikeRequest 16_03/1603PatchF/1603PatchF 16_03/1603PatchG/1603PatchG 16_03/1603PatchG/DR 16_03/1603PatchG/RadioConfigIni/Reband/Fallback17/Pri1Sec3 16_03/1603PatchH/1603PatchH 16_03/1603PatchI/1603PatchI 16_03/1603PatchI/NFareboxSubsystemIni/Build16 16_03/1603PatchI/systemIni/Build16/Fixed 16_03/1603PatchK/1603PatchK 16_03/1603PatchL/1603PatchL 16_03/1603PatchL/NAnnunciatorSubsystemIni/BikeRequest 16_03/1603PatchM/1603PatchM 16_03/1603PatchN/1603PatchN 16_03/1603PatchP/1603PatchP 16_03/1603PatchP/Logging/dmp 16_03/1603PatchP/NEthernetCommDriverIni/FieldDebug 16_03/1603PatchP/NGprsSubsystemIni/Fallback17 16_03/1603PatchP/NMotorolaSB9600RadioDriverIni/Reband 16_03/1603PatchP/systemIni/Build16/Fixed_Cellular

    Source String.JPG

    DataFields.JPG

    I know this is a unique request, I have gotten close by trial and error, but I just couldn't quite get it. If needed additional helper columns can be created to get the final result desired.
    Truly thank you in advance for any assistance that can be provided.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,303

    Re: Excel 2016 Custom String Trim in a Cell

    Hi Burnrose and welcome to the forum,

    If you attach a sample workbook with your picture data above and tell us what the final answer is supposed to be, I'm sure we can twiddle up an answer. Read the yellow banner at the top to see how to attach a workbook.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,755

    Re: Excel 2016 Custom String Trim in a Cell

    Hi & welcome to the board.
    If you are using a PC, how about
    Formula: copy to clipboard
    =FILTERXML("<k><m>"&SUBSTITUTE(A2,"/","</m><m>")&"</m></k>","//m[contains(.,'Patch')][last()]")

  4. #4
    Registered User
    Join Date
    03-21-2022
    Location
    Las Vegas
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Excel 2016 Custom String Trim in a Cell

    Fluff13!!!! You are a beautiful person!!! This works absolutely flawless! Thank you so much for you fast and accurate assistance!

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,755

    Re: Excel 2016 Custom String Trim in a Cell

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2016-Custom Function not available to use in-cell
    By trashcan21 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-06-2019, 05:58 AM
  2. [SOLVED] Excel 2016 Custom formatting 18/04 YY/MM
    By clairh2011 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2018, 10:04 AM
  3. [SOLVED] Trying to get excel to custom trim information
    By WTRedmond in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2017, 04:09 PM
  4. [SOLVED] Remove duplicates in Excel 2016 text string in one cell
    By martin7b in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2017, 02:07 AM
  5. Custom Globe (3d Maps Excel 2016)
    By Skagyeix in forum Excel General
    Replies: 0
    Last Post: 04-05-2017, 06:04 AM
  6. Why won't Excel 2016 display my custom ribbon?
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2015, 04:20 PM
  7. [SOLVED] Trim spaces before and after string in cell
    By xbr088 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2013, 01:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1