+ Reply to Thread
Results 1 to 11 of 11

Excel trim / truncate function

  1. #1
    Forum Contributor
    Join Date
    03-27-2015
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    141

    Excel trim / truncate function

    I've a listing of folder files in column A as listed below, and would like to know whether trim / truncate function can be applied to extract the numeric figures in brackets at the end of the file name.

    28/04/2015 10:20 AM 509,195 12035-HEC08-8007 Cert 16 (S15-0008) $1,031,827.63 (1031827.63).pdf
    16/12/2014 04:35 PM 499,507 12056-HEC10-8008 Cert 20 (S14-0036) $579,464.21 (579464.21).pdf
    14/12/2017 09:46 AM 478,162 12056-HEC10-8008 Cert 21 (S17-0013) $1,255,073.52 (12055073.52).pdf
    02/12/2014 10:43 AM 507,966 12064-HEC10-8021 Cert 17 (S14-0044) $1,681,791.99 (1681791.99).pdf
    16/12/2014 04:31 PM 532,795 12070-HEC11-8012 Cert 27 (S14-0192) $1,008,488.31 (1008488.31).pdf
    25/09/2017 10:34 AM 894,632 12070-HEC11-8012 Cert 28 (S17-0010) $3,023,000 (3023000).pdf
    29/12/2017 10:21 AM 553,298 12070-HEC11-8012 Cert 29 (S17-0016) $1,305,460.7 (1305460.70).pdf
    16/12/2014 04:36 PM 540,225 12073-HEC11-8016 Cert 18 (S14-0030) $448,588.78 (448588.78).pdf

    Say for first line, I wish to extract the numeric figure under bracket for 1031827.63 to be show in column B (next to column A)
    and for second line, 579464.21 to be shown in column B, and so on.

    Kindly see if someone could help.

    Many thanks indeed.

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,956

    Re: Excel trim / truncate function

    Perhaps: =--MID(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),FIND(".pdf",SUBSTITUTE(A1," ",REPT(" ",50)))-50,LEN(A1))),2,LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),FIND(".pdf",SUBSTITUTE(A1," ",REPT(" ",50)))-50,LEN(A1))))-6)

  3. #3
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Excel trim / truncate function

    B1=trim(left(substitute(right(substitute(a1,"(",rept(" ",50)),50),")",rept(" ",50)),50))

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Excel trim / truncate function

    Cell B1 formula , drag down

    HTML Code: 
    Last edited by wk9128; 08-02-2021 at 10:33 PM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel trim / truncate function

    An alternative means is to extract the data with Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Excel trim / truncate function

    Or this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Diana Tanase


    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Excel trim / truncate function

    Try

    =--LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",44)),88),44)

  8. #8
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Excel trim / truncate function

    --trim(left(right(substitute(a1,"(",rept(" ",len(a1))),len(a1)),len(a1)-5))

  9. #9
    Forum Contributor
    Join Date
    03-27-2015
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Excel trim / truncate function

    Thank you indeed for all your suggestions, and in fact I've already tested most of them and posted the result several days ago with Quick Reply.
    Possibly I've clicked the wrong button when I left this forum as I cannot find my reply today.

    In any case, not all of your suggestions work all the times when I fit in some of my dummy data to the formulas. I cannot fully understand every logic behind the formulas, and don't know where the error comes from.

    I'll have to further check which formula can best suit my need by testing with more dummy data, and come back with my final decision on which one to choose.

    Many thanks again.

  10. #10
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Excel trim / truncate function

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Excel trim / truncate function

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Trim Function not Working in Excel 2013
    By maribeth721 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-03-2017, 12:04 PM
  2. Excel Function to trim cell data
    By andrew8008 in forum Excel General
    Replies: 3
    Last Post: 01-12-2009, 01:21 AM
  3. Need LEN(TRIM()) equivalent one excel function
    By suman.biswas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2008, 04:02 AM
  4. Trying to trim a trailing space char and the Trim function isn't working
    By Psychochook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2007, 11:28 PM
  5. Excel Trim , Clean or IsNonText function doesn't work
    By munzer1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2007, 06:25 PM
  6. Replies: 3
    Last Post: 12-29-2005, 11:10 PM
  7. Problem with TRIM function in Excel
    By Julie Beth in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2005, 01:05 PM

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