Count characters of sentence

[satya12]

Hi All,
Help me , how to write VBA Code for this
Here i am having sentences but those sentence will not go above 40 for one column , above 80 for another column.
If you see "CTO 2TB 7200 SATA Enterprise 3.5 in " this is the sentence for example .
if this sentence count greater than 40 , we have to delete the characters right to left until this sentence count will 40.
same as another columns if sentence character count greater than 80 remove the characters from right to left until the sentence meet the count80.
40 characters Columns : B for example
80 Characters Columns : C for example
Please help me for this

 

[Joe4]

Can't you just use the LEFT function to take the left-most 40 or 80 characters?

The LEFT function exists in BOTH Excel and VBA.

 

[satya12]

Thanks for your help!
But, how to write vba code for if count is greater than 40 remove characters which are above

 

[Joe4]

Actually, if you want to fix the whole column at once, it would be quicker to use "Text to Columns", and select Fixed Width, and drop everything after spaces 40 and 80, respectively.
If you were applying this to columns B and C, the code would look something like this:

Sub MyMacro()

'   Cap column B at 40 spaces
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(40, 9)), TrailingMinusNumbers:=True
        
'   Cap column C at 80 spaces
    Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(80, 9)), TrailingMinusNumbers:=True
        
End Sub