+ Reply to Thread
Results 1 to 7 of 7

Clean/Trim Macro Length Limitation

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Question Clean/Trim Macro Length Limitation

    Hey, guys!

    I found a macro on the web that does a trim/clean on selection of cells - see below. But the thing is it does NOT work on cells which are longer than roughly 1 sentence or so. Meaning as soon as there's e.g. roughly 2 sentences in a cell or more - it replaces cell's contents with #VALUE (instead of doing trim/clean).

    Could any one advice how to fix this?


    Please Login or Register  to view this content.
    Last edited by splendidus; 12-23-2021 at 10:31 AM.
    Office 2019 16.0.13205.200000 64-bit

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Clean/Trim Macro Length Limitation

    Could you provide a sample workbook with some data where this throws up the error? I've tried it on data with 10 and 20 sentences in some cells and it works fine for me.

    BSB

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Re: Clean/Trim Macro Length Limitation

    Quote Originally Posted by BadlySpelledBuoy View Post
    Could you provide a sample workbook with some data where this throws up the error? I've tried it on data with 10 and 20 sentences in some cells and it works fine for me.

    BSB
    There's a sample right HERE (the attach file here does not work for me).

    Basically what I did is took the very same macro as above, and the two sentences from my initial post. When macro is run it replaces the 2 sentences in the cell with #VALUE. I tested it on 2 computers, result's the same.

    But don't get me wrong, it might as well be that it runs perfectly on your end and only doesn't work for me - all good. But I'd love to understand how to fix it, so it works for me as well.


    I'd really appreciate any advice on this :]

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Clean/Trim Macro Length Limitation

    Put simply, if your text strings are over 255 characters long then this method will fail.

    BSB

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Re: Clean/Trim Macro Length Limitation

    Quote Originally Posted by BadlySpelledBuoy View Post
    Put simply, if your text strings are over 255 characters long then this method will fail.

    BSB
    Absolutely, I did figure out that issue/limitation's indeed - length. Any chance you might be able to suggest a way to adjust the macro to fix this issue (=remove length limitation)?
    Last edited by splendidus; 12-23-2021 at 03:35 PM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Clean/Trim Macro Length Limitation

    Change to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Re: Clean/Trim Macro Length Limitation

    Quote Originally Posted by jindon View Post
    Change to
    Please Login or Register  to view this content.
    That works like a charm! Thank you so, so, so very much, Jindon!!

    And purely for posterity, the final, working macro is below.





    Please Login or Register  to view this content.

+ 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] Add trim clean to my formula
    By rayted in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-21-2021, 08:07 AM
  2. [SOLVED] macro to use for excel to clean and trim coumns a2:a100
    By David713 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-07-2017, 12:02 PM
  3. Using TRIM, SUBSTITUTE & CLEAN
    By scarames in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-18-2014, 03:13 PM
  4. [SOLVED] Clean and Trim Not Working
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2013, 07:14 PM
  5. [SOLVED] Clean Trim Proper Function Using a Macro
    By kamalthakur in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2012, 09:06 PM
  6. Formula Length Limitation
    By Ivkosky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2010, 05:25 PM
  7. Clean and Trim
    By tl7410 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2009, 03:03 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