+ 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
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,187

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    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

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and 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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    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)

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