Truncate

  • Thread starter Thread starter Elizabeth
  • Start date Start date
E

Elizabeth

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
 
With text in A1, in another cell enter:

=LEFT(A1,200)

You can also use a macro to truncate "in place"
 
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.
 
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 special>values>ok>esc

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


Gord Dibben MS Excel MVP
 
Back
Top