Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Character length in a cell or cells

11 Jan 2015   #1
synth

Windows 7 Ultimate x64
 
 
Character length in a cell or cells

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


My System SpecsSystem Spec
.
11 Jan 2015   #2
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

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
My System SpecsSystem Spec
11 Jan 2015   #3
synth

Windows 7 Ultimate x64
 
 

Quote   Quote: Originally Posted by Ranger4 View Post
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?
My System SpecsSystem Spec
.

11 Jan 2015   #4
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

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.
My System SpecsSystem Spec
11 Jan 2015   #5
Callender

Microsoft Windows 7 Home Premium 64-bit 7601 Multiprocessor Free Service Pack 1
 
 
Excel Add In

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.
My System SpecsSystem Spec
12 Jan 2015   #6
Callender

Microsoft Windows 7 Home Premium 64-bit 7601 Multiprocessor Free Service Pack 1
 
 
Solution

Quote   Quote: Originally Posted by synth View Post
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
I've managed to try the suggestion that I posted earlier and it works.

Screenshot:
Character length in a cell or cells-asap-1.jpg
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.


My System SpecsSystem Spec
Reply

 Character length in a cell or cells




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Can I compare 2 Excel spreadsheets cell by cell?
I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always ...
Microsoft Office
6 Cell Vs 9 Cell Battery for Laptop?
Someone needs to get a new battery for a very old laptop they have. The battery doesn't work so they have to have it plugged all the time otherwise it wouldn't work. Laptop is 15.6 and sort of heavy but the thing is they never take the laptop outside the house. It will mainly be used on one floor...
Hardware & Devices
Locking certain cells from updating
All, I created a five year pro forma financial model for my business. The spreadsheet makes extensive use of worksheet formulas and macros. I also added a sheet to track actuals (numbers extracted, by hand, from QuickBooks monthly). Certain formulas in the spreadsheet are based on values...
Microsoft Office
Cells contaning Fri 16 Jul 10 and similar as text
I have a s/sheet ( Excel2007), whose source data was taken from a link. The date column is in the format as per title. In that form it will not sort itself or associated columns in to date order. Has anyone a way to make the data in the column sort itself as I want? Also with the example...
Microsoft Office
Excel un password cells
Hi I have a excel sheet that some cells are password protected i have no idea what the password is any ideas how i can find the password or any software that will find it, so i can change the cell contents. Thanks
Microsoft Office
Slow deletes (character by character)
This probably has nothing to do with Windows 7, but I thought someone on this forum would know. When in a text editor like on this forum or on Microsoft word and when I delete and hold it down it will be really slow. Characters would only delete every half a second no matter what. Anyone...
General Discussion


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 07:33.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App