Share via

Truncating text in a Macro

Anonymous
2011-09-26T21:27:37+00:00

We currently get a file from our customers with pricing data of their products.  On the worksheet for example we have two descriptions of the product. I'm currently creating a macro to format the worksheet to my specifications.  Often there are times that the text in this description column is too long.  The description is assigned to column D.  What I need is for when I run the macro it deletes everything to the right after 60 characters. Can this be done? If so what would be the code? Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-09-27T15:36:58+00:00

That works greats! Now how would I add a range for all of column D, how would I write that? Thanks so much!

Hi,

Like this

Sub Truncate_ColD()

Dim c As Range, MyRange As Range

Dim LastRow As Long

LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row

Set MyRange = Range("D1:D" & LastRow)

For Each c In MyRange

    c.Value = Left(c.Value, 60)

Next

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-09-27T14:42:47+00:00

    Let me clarify what I need. I don't want to have to add another column to get what I'm looking for. I would like for the data in the orignal column to only be 60 characters and anything more should be deleted. Can this be done?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-09-27T14:36:31+00:00

    Where do I put that function? When I try to ensert into the cell I just get "= Left(YourText, 60)".

    Do I have to enter the text whree it says "yourtext"? This won't work if I have to do that for every row.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-09-26T21:55:22+00:00

    Create a macro and use the LEFT() function to select the first 60 char in the field

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-09-26T21:44:14+00:00

    Use the Left function...

    YourText = "A lot of text the ends up being more than than 60 characters long"

    TruncatedText = Left(YourText, 60)

    Was this answer helpful?

    0 comments