Macro to reduce characters in a column to 30 max

[tonywatsonhelp]

Hi Everyone,

I need a macro that can look down column C and if any of the cells contain more than 30 characters reduce it by simple making it only 30 chrs long,

So "abcdefghijklmnopqrstuvwxyz1234567890"
would become

"abcdefghijklmnopqrstuvwxyz1234"

please help if you can
thanks

Tony
​

 

[Joe4]

Easiest way is to use Text To Columns, choose the Fixed Width option, set the break after 30 spaces, and elect to not import the second field.
If you do this with the Macro Recorder turned on, you will have the VBA code that you need.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub MaxOf30CharactersInColumnC()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("IF(" & .Address & "="""","""",TRIM(LEFT(" & .Address & ",30)))")
  End With
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Easiest way is to use Text To Columns, choose the Fixed Width option, set the break after 30 spaces, and elect to not import the second field.
If you do this with the Macro Recorder turned on, you will have the VBA code that you need.

I was originally going to propose that, but changed my mind as it could leave one (or more) trailing spaces if the text is in sentence form with spaces separating the "words" in it.

 

[Joe4]

I was originally going to propose that, but changed my mind as it could leave one (or more) trailing spaces if the text is in sentence form with spaces separating the "words" in it.

Not sure I follow. Are you saying it could leave 31 characters?
Care to post an example?

 

[Rick Rothstein]

Not sure I follow. Are you saying it could leave 31 characters?
Care to post an example?

Sorry, I stand corrected... I was wrong. I thought that if the 30th character was a space, then it would be retained at that position, but I see that I was wrong about that... the trailing spaces are removed (that is what I get for trying to do something from"memory"). That leads me to ask... what if you needed to retain the trailing space for some future purpose... how would you do that? Selecting the Text option button does not seem to do that (even if you format the destination cells as Text beforehand).

 

[tonywatsonhelp]

Thanks Rick, thanks Joe, I'm going to use Ricks VBA as it works great

Tony

 

[Rick Rothstein]

Thanks Rick, thanks Joe, I'm going to use Ricks VBA as it works great

The code Joe was trying to drive you to would also have worked great as well. This is what he had in mind...

Sub MaxOf30CharsInColC()
  Application.DisplayAlerts = False
  Columns("C").TextToColumns , xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(30, 1))
  Application.DisplayAlerts = True
End Sub

 

[tonywatsonhelp]

Hi Rick,
That's actually a lot quicker, so a big thank you again and to Joe for suggesting the Idea (Which I didn't fully understand at first sorry)

Great

Tony

 

[Joe4]

what if you needed to retain the trailing space for some future purpose... how would you do that? Selecting the Text option button does not seem to do that (even if you format the destination cells as Text beforehand).

Rick,
i did some testing, and neither my method nor yours would retain that trailing space. Hopefully, it isn't an issue.

a big thank you again and to Joe for suggesting the Idea (Which I didn't fully understand at first sorry)

You are welcome. I was trying to teach you a handy tool that helps lead to some self sufficiency. The Macro Recorder is a great tool to get snippets of code, without having to write any of it yourself. If you turn on the Macro Recorder and record yourself performing an action, it will give you the VBA code needed to do that. Sometimes, a little clean-up may be required, as it is very literal and also records things like cell selection and scrolling, but it gives you the guts of a lot of the code you need! This is a perfect example of a problem in which this tool would help.