Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro for column width (Excel 2000)

    Is it possible to write a macro in Excel that will count the characters in a cell to make sure they are only a specific number, and if not, remove the extra characters? Say we have a field we are uploading data to, that will accept only 11characters, but could have data in the Excel spreadsheet (the applicable cell), that exceeds the 11
    character limit. Is there a way to automate the process of cutting off the 12th character forward?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for column width (Excel 2000)

    You can use Validation for this.
    - Select the cell(s) involved.
    - Select Data | Validation...
    - In the Allow dropdown list, select Text Length.
    - In the Data dropdown list, select Less than or equal to.
    - In the Maximum box, enter 11.
    - You can set an instruction message and an error message in the other tabs of the dialog.

    Note: Validation will still allow users to override the warning.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for column width (Excel 2000)

    If you set the Error Alert to Stop, the only override available to Users is to refuse to enter any data in the target cells.
    Gre

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for column width (Excel 2000)

    Good point, thanks!

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for column width (Excel 2000)

    Thank you.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for column width (Excel 2000)

    This suggestion did not work for me. I will be uploading hundreds of records and I need to automatically drop off text that is greater that 11 characters. By using Data, Validation, I was stopped with a dialog box and error. Any suggestions?

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for column width (Excel 2000)

    Is it possible to import your data first, then do some trimming? If so, a macro something like this will perform the required trimming on all the cells in a selected range:

    Public Sub TruncateText()
    Dim Cell
    For Each Cell In Selection
    Cell.Value = Left(Cell.Value, 11)
    Next Cell
    End Sub

    This could be further "automated" to work over a predetermined range e.g. a particular column.

    Alan

Posting Permissions

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