+ Reply to Thread
Results 1 to 10 of 10

Truncate Contents of a Cell from the Right

  1. #1
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Truncate Contents of a Cell from the Right

    Hello! My goal is to truncate the contents of a cell to the 2nd period, coming from the end of the string (right to left). For example, if the string is "AB.CD.EF.GH.IJ.KL" I would like to trim it to "AB.CD.EF.GH" (removing the period after GH as well). Also, I need to perform this without using text-to-columns (the way I've been doing it) because I have hundreds of thousands of cells to truncate, and the number of periods in each cell varies greatly.

    I've researched and haven't found anything that will trim the string from the right, to the 2nd period. Is there something simple that can do this?

    Thanks!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,191

    Re: Truncate Contents of a Cell from the Right

    Is it always 2 characters between the period? It would help if you gave a few more examples of your starting strings.

    Pete

  3. #3
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Re: Truncate Contents of a Cell from the Right

    Apologies, it's actually a long file path, and i need to remove the file name at the end. So actually, I need to trim to the first "/". So for instance:

    file://entshare/Shared/Everyone/08 EEMP GRADS/AA QA Automation Results/Encompass Automation/JanKRI.xls

    And i want to remove the /JanKRI.xls

    But some paths are much shorter and much longer. Sorry for the confusion.

  4. #4
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Re: Truncate Contents of a Cell from the Right

    Using text to columns becomes cumbersome because each path has varying amounts of "/"

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

    Re: Truncate Contents of a Cell from the Right

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Re: Truncate Contents of a Cell from the Right

    Thanks for the reply, but that is returning #VALUE!

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

    Re: Truncate Contents of a Cell from the Right

    This is what I get

    Excel 2013 32 bit
    A
    1
    file://entshare/Shared/Everyone/08 EEMP GRADS/AA QA Automation Results/Encompass Automation/JanKRI.xls
    2
    3
    file://entshare/Shared/Everyone/08 EEMP GRADS/AA QA Automation Results/Encompass Automation
    Sheet: Quote

    Excel 2013 32 bit
    A
    3
    =LEFT(A1,FIND("~|",SUBSTITUTE(A1,"/","~|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)
    Sheet: Quote

  8. #8
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Re: Truncate Contents of a Cell from the Right

    Yes, that looks like what I need. I pasted directly into my document, but I'm still getting #VALUE! Could it perhaps be formatting issue? Any other ideas? Thanks again..

  9. #9
    Registered User
    Join Date
    01-19-2016
    Location
    Jacksonville, FL
    MS-Off Ver
    2013
    Posts
    22

    Re: Truncate Contents of a Cell from the Right

    Actually I got it to work. THanks a lot!

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

    Re: Truncate Contents of a Cell from the Right

    Glad you sorted it & 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. [SOLVED] Truncate cell ONLY if it contains string?
    By NewYears1978 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-18-2018, 12:23 PM
  2. [SOLVED] Truncate Mulitple Words in a Cell
    By UncleKevy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-06-2014, 12:10 AM
  3. Need to truncate a cell if the character length is over 100
    By Xaos in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 02:09 PM
  4. How to truncate the content of a cell?
    By vvvv in forum Excel General
    Replies: 2
    Last Post: 01-29-2007, 05:22 AM
  5. Truncate cell content after a certain character
    By brakai295 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2006, 07:33 AM
  6. [SOLVED] Truncate number in cell
    By Chris Hedlund in forum Excel General
    Replies: 5
    Last Post: 05-12-2006, 11:30 AM
  7. Truncate Cell text all the time
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2005, 01:05 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