Ozgrid Excel Help & Best Practices Forums


XL Templates | XL Add-ins | XL Training | XL Estimating | XL Scheduling | XL Recovery | XL Trading | XL Financial | XL Conversion | XL Charting


+ Reply to Thread
Results 1 to 6 of 6

Thread: Chomp / Remove Characters after certain # of characters reached?

  1. #1
    Join Date
    18th April 2005
    Posts
    40

    Chomp / Remove Characters after certain # of characters reached?


    Download Active Data For Excel > > DETAILS > >
    Hi,

    Is there a way to "chomp" or remove excess characters from a cell after a certain number of characters have been reached?

    For instance, say Column D has 500+ cells, all of which contain alphanumeric data (about 70 characters including spaces). I need to mass-remove (VBA?) the characters that come after the first 55 characters. The first 55 characters (including spaces) would stay in place.

    Make sense? If not, please let me know and I'll try my best to reword the question.

    Thanks a bunch guys n gals,
    EC
    Last edited by essential_clix; March 13th, 2006 at 08:09.

  2. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,032

    Re: Chomp / Remove Characters after certain # of characters reached?

    Hi EC,

    Supposing your data is in Col A then a formula version could be placed in Col ?

    [vba]
    =IF(LEN(A1)>55, LEFT(A1,55), A1)
    [/vba]

    This will echo a 55 char string from Col A. To trim it to values you would use Copy/Patespecial/Values/

    A VBA version would be:

    [vba]
    Sub fiftyfive()

    ' Get rownumber of lastrow of data Col A
    lastrow = Range("a65536").End(xlUp).Row

    ' Check row 1 to last row #, Col A
    For i = 1 To lastrow
    ' If longer than 55 chars
    If Len(Cells(i, 1)) > 55 Then
    ' Cut to 55 chars
    Cells(i, 1) = Left(Cells(i, 1), 55)
    End If
    ' Not longer than 55 - get next row
    Next i

    End Sub
    [/vba]

    This version would replace the original values. Change the line:

    [vba]
    ' Cut to 55 chars
    Cells(i, 1) = Left(Cells(i, 1), 55)

    to

    Cells(i, 2) = Left(Cells(i, 1), 55)

    [/vba]

    and it adds the new values to Col B...


    Cheers,

    dr

  3. #3
    Join Date
    18th April 2005
    Posts
    40

    Re: Chomp / Remove Characters after certain # of characters reached?

    Hey thanks a million, man! The first code worked like a charm!

    Thanks again and have a good one!

  4. #4
    Join Date
    26th July 2004
    Posts
    1,035

    Re: Chomp / Remove Characters after certain # of characters reached?

    Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>>VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    3,015

    Re: Chomp / Remove Characters after certain # of characters reached?

    Hi

    =LEFT(A1,LOOKUP(9.999E+307,FIND(" ",A1&" ",ROW($A$1:$A$55))))

  6. #6
    Join Date
    18th April 2005
    Posts
    40

    Re: Chomp / Remove Characters after certain # of characters reached?


    Create Excel dashboards quickly with Plug-N-Play reports.
    Quote Originally Posted by pangolin
    Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>>VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)
    Man, I love learning new tricks like that! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Possible Answers

  1. Remove Last x Characters From String
    By ExcelChicky in forum EXCEL HELP
    Replies: 3
    Last Post: January 22nd, 2008, 05:55
  2. Replies: 5
    Last Post: January 16th, 2008, 11:30
  3. Remove Characters From End Of String Only
    By Benny Hays in forum EXCEL HELP
    Replies: 6
    Last Post: December 22nd, 2006, 07:21
  4. Remove Characters from a string
    By Robert B in forum Excel and/or Access Help
    Replies: 2
    Last Post: May 5th, 2006, 18:50
  5. Remove characters
    By suahm in forum EXCEL HELP
    Replies: 2
    Last Post: April 27th, 2006, 21:30

Bookmarks

Posting Permissions

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