+ Reply to Thread
Results 1 to 7 of 7

Trim Characters then find Min and Max

  1. #1
    Forum Contributor
    Join Date
    03-03-2011
    Location
    Ottawa, Ontario
    MS-Off Ver
    365
    Posts
    131

    Trim Characters then find Min and Max

    I have a column of cells with values such as AAA-13-0001, AAA-13-0002 etc. I need a macro to trim the AAA-13- and just leave the last 4 numbers 0001. Then I need to find the Min and Max number from this column and paste it into a 2nd worksheet (C1 and C2). I guess I have to change the column from text to numeric first but I am just starting out with VBA and just need a place to start and could probably figure it out if someone could point me in the right direction. Thanks for any help.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,879

    Re: Trim Characters then find Min and Max

    Use this function to get the last 4 digits and convert them into a number:

    Please Login or Register  to view this content.
    Replace A1 with your cell reference.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,961

    Re: Trim Characters then find Min and Max

    See the attached file.

    The format of the cells is 0000.

    Do you have questions or comments, just ask or comment.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    03-03-2011
    Location
    Ottawa, Ontario
    MS-Off Ver
    365
    Posts
    131

    Re: Trim Characters then find Min and Max

    That was basically what I needed but I didn't explain it all that well I guess. I was hoping for a macro as the column I need to trim will have a different number of entries each time so I need to scroll through all of the column and trim each entry then find the min and max then paste to another sheet. Thanks for the help.

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Trim Characters then find Min and Max

    If you leave it as text it won't be able to determine a min and max. If you need the leading zeroes just custom format the field as 0000.

    Please Login or Register  to view this content.
    edit: litlle late on my post so others may have said what i already mentioned

  6. #6
    Forum Contributor
    Join Date
    03-03-2011
    Location
    Ottawa, Ontario
    MS-Off Ver
    365
    Posts
    131

    Re: Trim Characters then find Min and Max

    Works perfect with a little tweaking. Thanks for all the help

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,449

    Re: Trim Characters then find Min and Max

    VBA?

    This can be done with the formula....
    vba with no loop.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1