A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Truncate



 
 
Thread Tools Display Modes
  #1  
Old November 18th, 2009, 08:59 PM posted to microsoft.public.excel.misc
Elizabeth
external usenet poster
 
Posts: 208
Default Truncate

I've never used this function before and frankly read all the instructions
and still dont understand it. I have a customer that doesn't want more than
200 characters in his field, presently there are many cells with over the
200. I was told by him to do this function, as a customer I didnt want to
keep asking anymore questions.

Is there a way someone can explain "as if Im 5" how to do this funcition?
Thank you so much
  #2  
Old November 18th, 2009, 09:04 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default Truncate

=LEFT(A1,200)
--
David Biddulph

"Elizabeth" wrote in message
...
I've never used this function before and frankly read all the instructions
and still dont understand it. I have a customer that doesn't want more
than
200 characters in his field, presently there are many cells with over the
200. I was told by him to do this function, as a customer I didnt want to
keep asking anymore questions.

Is there a way someone can explain "as if Im 5" how to do this funcition?
Thank you so much



  #3  
Old November 18th, 2009, 09:06 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 7,584
Default Truncate

With text in A1, in another cell enter:

=LEFT(A1,200)

You can also use a macro to truncate "in place"
--
Gary''s Student - gsnu200908


"Elizabeth" wrote:

I've never used this function before and frankly read all the instructions
and still dont understand it. I have a customer that doesn't want more than
200 characters in his field, presently there are many cells with over the
200. I was told by him to do this function, as a customer I didnt want to
keep asking anymore questions.

Is there a way someone can explain "as if Im 5" how to do this funcition?
Thank you so much

  #4  
Old November 18th, 2009, 09:19 PM posted to microsoft.public.excel.misc
Paul C
external usenet poster
 
Posts: 202
Default Truncate

Truncate is a number function and just removes the decimal portion of a
number. I don't think this is what you want.

if you want to trim a cell entry to 200 characters use the left function
=left(C1,200)
If you enter this formula in cell C2 it would return as a result the first
200 Characters of whatever text is in C1

If C1 contains a lot of extra spaces like "We have extra spaces
between words" you can use the trim function to remove all but one space
between words first and then shorten to 200 characters.
=Left(Trim(c1),200)

For cells with less than 200 characters this would have no impact.

to clean an entire column put this formula in the first row of a column next
to the column you wish to clean up. Copy the formula down to the last row
used then copy the column containing the formulas and then use Paste
Special-Values to paste over the original data. You can then delete the
formula and you have clean data with no more than 200 characters in a cell.



--
If this helps, please remember to click yes.


"Elizabeth" wrote:

I've never used this function before and frankly read all the instructions
and still dont understand it. I have a customer that doesn't want more than
200 characters in his field, presently there are many cells with over the
200. I was told by him to do this function, as a customer I didnt want to
keep asking anymore questions.

Is there a way someone can explain "as if Im 5" how to do this funcition?
Thank you so much

  #5  
Old November 18th, 2009, 10:50 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Truncate

TRUNC is for numbers only..........tells you that in help.

"Truncates a number to an integer by removing the fractional part of the
number"

What you need is LEFT function.

=LEFT(A1,200) will pull first 200 characters from A1

Use a helper cell with that formula for each of the cells containing more
than 200 chars.

Then copy/paste specialvaluesokesc

Paste overtop of original cells or in another range for customer to use.


Gord Dibben MS Excel MVP

On Wed, 18 Nov 2009 11:59:04 -0800, Elizabeth
wrote:

I've never used this function before and frankly read all the instructions
and still dont understand it. I have a customer that doesn't want more than
200 characters in his field, presently there are many cells with over the
200. I was told by him to do this function, as a customer I didnt want to
keep asking anymore questions.

Is there a way someone can explain "as if Im 5" how to do this funcition?
Thank you so much


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.