Results 1 to 7 of 7

Truncating Text Data within Cells

This is a discussion on Truncating Text Data within Cells within the Excel Questions forums, part of the Question Forums category; I have a column of 4000 cells with text data of varying size in each cell. I only need the ...

  1. #1
    Guest

    Default

    I have a column of 4000 cells with text data of varying size in each cell. I only need the first 8 characters in each cell and I want to truncate (trash) the rest of the data. Any ideas. I tried to set up a validation in a new column, but this did not work. Thanks in advance.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Select the 4000 cells, go to Data, Text to Columns, select "Fixed With", and drag a line that divides the first 8 characters from the rest. Click Finish.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Guest

    Default

    Thanks Juan, This moves the remaining charaters into the adjacent column, is there any way to have it just delete the left over characters?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Sure. Instead of clicking "Finish", click "Next", then, you have the option to "Format" each column. In there, select the second column, and choose "Skip this column". Then you can click Finish.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Guest

    Default

    OK, maybe I'm just stupid (as you nod your head), but does this have anything to do with the destination field? When I hit skip, it simply deletes the characters that I need and not the ones that I don't. In this final step the next button is grayed out. urrrrgh.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    You're selecting the first column, try selecting the second one, and click "Skip".
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    New Member
    Join Date
    Jun 2010
    Posts
    1

    Smile Re: Truncating Text Data within Cells

    You could cleanly have this in a bordering column by entering below equation. I will assume you want to truncate text in A1:

    =left(a1,8)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com