Hi guys, just wanted to ask if any of you can help me out in excel. Basically what I wanna do is, if I copied a text or a sentence and I would want to paste it on a cell, i'd like the cell to remove any characters if it is over than 40 excluding spaces.
Any kind of help/advice would be appreciated
Have a look at this website & see if it helps you.
MS Excel 2010: Set up a cell to only allow up to a certain number of characters
Thanks for this, but this isn't exactly what I'm looking for. What I'm trying to do is, like whenever I paste some words or sentences, I need a function or formula that will automatically adjust the characters to less than 40 for example, when I paste the set of words on that specific cell or range of cells. Is this even possible?
While it might be possible to create a macro for doing that I can't see how it would be of use to you. If for example you have a sentence of 100 words in it & you only use 40 of them, then surely the meaning of the text is lost.
Perhaps as a work around you might just be able to copy a small section of the text & paste that, instead of the whole lot.
Well when you copy and paste it's windows clipboard that contains the data. I don't think that you can easily limit the amount of data that can be copied to the clipboard and even if you could it would be very hard to make it work in a way that it recognizes that you're copying data into Excel and limits the string length accordingly.
If you use an add in for Excel:
ASAP Utilities for Excel - Text » Delete all after a given number of characters... - ASAP Utilities, description of our Excel tools (English)
Take a look at the first two screenshots here:
ASAP Utilities for Excel - Descriptions of all tools in ASAP Utilities (English)
The first one shows the toolbar and the "Text" icon.
The second one shows the ASAP Utilities "Menu" button.
You can highlight a cell or range of cells and run a text utility like the one in the first link to remove characters after whatever string length you specify.
I think Ranger4 gave you a decent answer but as far as I can work out you'd need to paste the data then modify it.
Note: I don't have MS Office on my home computer but I do use ASAP Utilities on my work machine so cannot post specific instructions or screenshots.
I've managed to try the suggestion that I posted earlier and it works.
Screenshot:
Paste the text into your spreadsheet. Highlight a cell or multiple cells - you know how to do that, right?
Open the text manipulation utility list in ASAP Utilities.
Select number 11 and replace all spaces in your selection with a unique character or symbol.
Select number 14 and trim the cell length.
Select number 11 and replace the unique character or symbol you chose with spaces.
You might get a better response and maybe someone could write a script if you explained how this is useful or the reasoning behind it.
Quote