+ Reply to Thread
Results 1 to 13 of 13

Option to shorten to a number of characters in cells.

  1. #1
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Option to shorten to a number of characters in cells.

    Happy new year everyone.

    I have a macro that saves selected columns from a Sheet to a new Workbook and save it as Text file.
    Now I want to create an option to shorten to a number of characters in all the cells. For example: users will enter a number, that number is a number of characters that all the cells can have maximum. If they enter 50, all the cells that have more than 50 characters will be cut at the 50th character and add some dots after that. If nothing entered, all the cells will still show full length.

    Any help would be appreciated.
    Thank you very much.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Option to shorten to a number of characters in cells.

    This is a simple question with some complex considerations. Is the truncating to x characters permanent or only a formatting thing? For example if I truncate to 50 characters, and then put in 70, do I need to get 20 characters back in each cell or can they be lost forever? Also how many cells total roughly are we talking about here (rows x column or total)? What about cells that havent been filled in yet, ie: cell A10 is empty and i enter 50 in as the limit, now I type or copy into cell A10...does it need to follow the same rule?
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Option to shorten to a number of characters in cells.

    haitran, thanks for attaching your file but where does the user enter the number of characters that all the cells can have maximum? And what cells does this maximum apply to? It would help to show examples of what you want the result to look like.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Quote Originally Posted by Zer0Cool View Post
    This is a simple question with some complex considerations. Is the truncating to x characters permanent or only a formatting thing? For example if I truncate to 50 characters, and then put in 70, do I need to get 20 characters back in each cell or can they be lost forever? Also how many cells total roughly are we talking about here (rows x column or total)? What about cells that havent been filled in yet, ie: cell A10 is empty and i enter 50 in as the limit, now I type or copy into cell A10...does it need to follow the same rule?
    Hi, the shorten to x characters is pernament, the users also have the original file so it won't matter if the cut off characters lost forever. Sorry if I confused you here but I want to shorten cells in the new Workbook that has saved Columns, not the original file.
    Quote Originally Posted by 6StringJazzer View Post
    haitran, thanks for attaching your file but where does the user enter the number of characters that all the cells can have maximum? And what cells does this maximum apply to? It would help to show examples of what you want the result to look like.
    The Shorten option will apply to all the cells in the new Workbook that has saved columns. And sorry I forgot to add the place to enter the number, I just added it in the file below, its Cell "E4". The result for a cell that has more than 50 (just an example) characters would be like "Input Seed Module Timings for Local Con...." (the "n" is the 50th character).
    Thank you
    Attached Files Attached Files
    Last edited by haitran; 01-03-2018 at 10:47 AM.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Option to shorten to a number of characters in cells.

    Give this a try. It checks every cell in column B of the txt file.

    Also, I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bug and runtime errors. I have added this to your code.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Quote Originally Posted by 6StringJazzer View Post
    Give this a try. It checks every cell in column B of the txt file.

    Also, I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bug and runtime errors. I have added this to your code.
    Thank you very much, it works.
    But because the columns order and number of columns can be changed, how can I check all the columns ? Thank you again.
    Last edited by haitran; 01-03-2018 at 11:23 AM.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Option to shorten to a number of characters in cells.

    Revised code to do all cells.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Thank you again.
    But is it possible for the macro to check and shorten all the cells before it saves to the new Workbook? At the moment the macro saves the selected columns to the new Workbook and then it does the shorten.
    Really appreciated your help.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Option to shorten to a number of characters in cells.

    Sorry, missed that, update attached. It appears that you have macro skills so hopefully you can take it from here.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Thank you very much for the help. :D

  11. #11
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Quote Originally Posted by 6StringJazzer View Post
    Sorry, missed that, update attached. It appears that you have macro skills so hopefully you can take it from here.
    Hi, I have some more questions about the same Macro, should I create a new thread or I can ask them here? Thank you very much.

  12. #12
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Option to shorten to a number of characters in cells.

    You can ask them here, although you might get a bigger audience if you start a new thread.

  13. #13
    Registered User
    Join Date
    12-10-2017
    Location
    Netherland
    MS-Off Ver
    2016
    Posts
    19

    Re: Option to shorten to a number of characters in cells.

    Quote Originally Posted by 6StringJazzer View Post
    You can ask them here, although you might get a bigger audience if you start a new thread.
    Hi, can you help me with this? I tried to create a new thread but it gets no replies for 2 days already so I think I can ask you here.
    I want to add 2 mandatory columns name COI and Workpackage.These 2 columns have to be always in the chosen columns (Column A in Sheet "Settings"). They are not from the Sheet "Inputfile" . Column COI is just the time create the new Workbook, for exp: all the cell of column COI should look like this "COI (12/21 13:03)", column Workpackage is a counting column for each row with format main. + number. For exp: main.1, main.2...
    Thank you very much.

+ 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. Formula to sort cells containing less & more than a number of characters
    By iantix in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2016, 12:46 PM
  2. [SOLVED] Count cells where characters 2 to 8 is a number
    By DD1 in forum Excel General
    Replies: 8
    Last Post: 08-16-2015, 09:17 PM
  3. [SOLVED] Shorten string length if exceeds 31 characters (max sheet name length)
    By r2fro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2014, 05:57 AM
  4. Summation of Number, Empty Cells and Cells with characters
    By iyounis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-02-2013, 08:29 PM
  5. How to count characters in a number of cells?
    By Staalander in forum Excel General
    Replies: 8
    Last Post: 01-11-2007, 11:36 AM
  6. How do I shorten a number?
    By swingkittenva in forum Excel General
    Replies: 3
    Last Post: 01-06-2006, 12:35 PM
  7. Find cells containing a specified number of characters
    By jdanker in forum Excel General
    Replies: 3
    Last Post: 08-23-2005, 04: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