Solved: Need to truncate Cell to keep it under 250 Character's

[frank_m]

HI,

I need the code given below, (or similar), modified so the cells existing value is kept under 250 Character's.

I need to remove characters from the beginning of the value only and I want the beginning to always start with "_Times Out as of : " (this will of course mean that the length will usually be truncated to considerably less than 250, but that is fine)
If ActiveCell.Row > 15 Then

Cells(10, 1).Value = Cells(10, 1).Value _
& "_Times Out as of : " & Int(Now()) & " is: " & Target.EntireRow.Cells(1).Value
'Some how truncate

End If

[Zack Barresse]

Hi there,

Not sure how you're calling this or how it's firing, but maybe you could use something like this ...

Sub CodeTestPlzzzz()

Const iMaxLen As Long = 250
Const sBegin As String = "_Times Out as of : "
Dim sValue As String, rCell As Range

Set rCell = Range("A10")
sValue = Right(rCell.Value, 231)

rCell.Value = sBegin & sValue

End Sub

HTH

[frank_m]

Thanks a lot for that Zack

Your code is nice and I can use it, but if its not to much trouble, I would like to trim the fat only from the left of an instance of "_Times Out as of : "

Sample String before truncating below
_Times Out as of : 1/12/11 Out Times Out as of : 1/13/11 Needs Calibration TurnsDuringThisUse:51 TotalTurns:(51)_Times Out as of : 2/6/11 Needs Calibration_ Times Out as of : 2/10/11 Needs Calibration TurnsDuringThisUse:2 TotalTurns:(53)_Times Out as of : 3/1/11 Needs Calibration_ Times Out as of : 3/2/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/3/11 Needs Calibration_ Times Out as of : 3/4/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/5/11 Needs Calibration_ Times Out as of : 3/6/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/7/11 Needs Calibration

(marked result in blue above. Red shows some of the characters removed that leaves a date illegible. I'd rather have no date, or remove several words than have only part of a word or phrase, or part of a date)
Result after running your code shown below:
Times Out as of : 1 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/5/11 Needs Calibration_ Times Out as of : 3/6/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/7/11 Needs Calibration

Preferred Result below (much shorter yes, but complete words and/or dates)
Times Out as of : 3/6/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/7/11 Needs Calibration

Edit: I have some inaccuracies with the characters that I colored red and blue and the end portion of sample string it's self, so please let me know if it's confusing you and I'll re-post with corrections.

[frank_m]

For some clarity, I'll point out that the significant mistake I made in the previous post is the preferred result.

I believe the result would be:

Times Out as of : 3/5/11 Needs Calibration_ Times Out as of : 3/6/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/7/11 Needs Calibration

[Zack Barresse]

But from your logic, your above string would only return...

_Times Out as of : 3/7/11 Needs Calibration

That is if you want to delete everything to the left of the last "_Times Out as of : "

How do you know which data parts you want to parse? Can you give us a little more logic to follow?

Edit: Also, in your sample data you have some parts that show "_ Times Out as of :" and some which show "_Times Out as of :". They are different. By only one character, but still different. Will you have both in your data? Will it be one or the other?

[frank_m]

Sorry Zack,

I haven't explained it well. I want to include as many instances of the phrase "_Times Out as of : " as possible, without going over the 250 char max and without leaving any partial words or partial dates.

With the sample string, the result should be:

Times Out as of : 3/5/11 Needs Calibration_ Times Out as of : 3/6/11 Needs Calibration TurnsDuringThisUse:0 TotalTurns:(53)_Times Out as of : 3/7/11 Needs Calibration

In other words if you count from the point where Times Out as of : begins, to the start of where it is repeated and count that as one complete block of text, I would like have as many complete blocks of text as possible, while trimming off the oldest blocks.

[Zack Barresse]

Okay, that makes much more sense. No worries, we'll get there. :)

What about the _ I posted about? Was it a typo, or will it always be right next to your text? Or could it be an underscore and then a space and then text?

[frank_m]

that is a typo the underscore is meant to be there, without a space was on either side of the underscore was my intention, but with a space is fine if one is easier to deal with than the other.
Thanks for baring with me in such a friendly manner :beerchug:

[Zack Barresse]

No worries. Okay, so I copied your text and put in in a cell (A1). I used this code and it output to A2. I think it's formatted how you want. And it will only take the sections which it can fit the entire thing.

Function TrimText(rngCell As Range) As String
Dim iLenTest As Long
Dim aVals() As String, iStep As Long
Dim sTemp As String, sLast As String
Const iMaxLen As Long = 250
Const sDelim As String = "_"
aVals = Split(rngCell.Value, sDelim)
sLast = "Could not fit data"
For iStep = UBound(aVals) To LBound(aVals) Step -1
sTemp = Trim(sTemp) & sDelim & Trim(aVals(UBound(aVals)))
iLenTest = Len(sTemp)
If iLenTest < iMaxLen Then
sLast = sTemp
Else
TrimText = sLast
Exit Function
End If
Next iStep
End Function

Sub TestTrimFx()
Range("A2").Value = TrimText(Range("A1"))
End Sub

Let us know what you think.

[frank_m]

Works like pure heaven Zack. - In fact the code nearly takes on a charisma of it's own for me, as i gaze at it, trying to understand how it all comes together to complete the task.

Thanks a bunch Sire :bow:

[Zack Barresse]

No problem. Although, I do see some inherent errors. Like if the overall length is under the maximum set you won't get a value. Also, it was actually just grabbing the last part of the array and duplicating it. I missed where I should have had a variable instead of the Ubound() portion. This might work a little better for that...
Function TrimText(rngCell As Range) As String

'Dimension variables
Dim iLenTest As Long
Dim aVals() As String, iStep As Long
Dim sTemp As String, sLast As String

'Set constants
Const iMaxLen As Long = 250
Const sDelim As String = "_"

'Put original data into array, parsing by set delimiter
aVals = Split(rngCell.Value, sDelim)

'Set the sLast variale, just in case the last item in the array is
'smaller than the max length (above constant)
sLast = "Could not fit data"

'Loop through the array data, from the last to the first
For iStep = UBound(aVals) To LBound(aVals) Step -1

'Set the temporary string to append the current data, plus the
'delimiter (specified above as a constant), and the current
'value in the array loop (i.e. the next set of text)
sTemp = sDelim & Trim(aVals(iStep)) & Trim(sTemp)

'Use this variable to test the overall length of the temporary
'string of parsed data
iLenTest = Len(sTemp)

'Test the length variable against the maximum set length (from
'the above constant)
If iLenTest < iMaxLen Then

'If the current temporary string is shorter than our overall
'maximum length, then we will reset the sLast variable,
'which is what we can use in subsequent loops
sLast = sTemp
Else

'If the current temporary string is longer than our overall
'maximum length, then we will just set the previous (sLast)
'string variable as our output and exit the function now

'####
'As to catch the error of the entire string being under the
'max limit we set, we'll put the code at the end of the
'function and just exit
GoTo ExitNow

End If

Next iStep

ExitNow:
TrimText = sLast

End Function

Edit: And I added comments for you.

[frank_m]

Wow

Thanks for having such a keen sense to see to it that your code is producing the correct results under various circumstance's

My eyes and brain haven't been functioning well at all today, so I'm not going to swear that your new version is completely bullet proof, but it does seem to be that, or at least very close to it. Very likely as close to perfect as I would ever need it to be. -- I did look more closely this time at the result and I tested it with two string's that were under 250 chars in length, (one about 150 , the other about 245 and both remained intact.


Thanks again Zack -- I really appreciate your dedication towards getting the code working as close to perfection as possible, considering the unusual layout and or limitations of the sample or other possible data string's.

Edit:
Oh almost forgot.. Thanks so much for adding all that code commenting.
-- That will help me considerably.

[Zack Barresse]

Oh, you are very welcome indeed. If you could get a small subset of data which we could test, I'd be more than happy to.

[frank_m]

If you could get a small subset of data which we could test, I'd be more than happy to.
Thanks - I will certainly take you up on that. It will be several days to get it ready for you, as I need to first work out some other final details and functionality of the workbook.