Keep the 1st 24 and last 7 characters of a text string

[Mr_Ragweed]

Hi all. I'm using excel2010. I've been searching the posts for a solution and everyhting always seems to be in reverse of what i need. I have text strings of varying length and are all unique. All i want to do is keep the first 24 characters and the last 7 characters (=31 so i can name the worksheet tab with the result). It's part of a loop that first replaces all invalid characters, then does a LEN count and removes all spaces anytine LEN>31. The next part of the loop is to recount the character length and anything still over 31 characters edit down in the way i described.
I kinda get the formula version in that =Left(A2, 24) would give me the first 24 and =Right(A2, 7) would give me the last seven. However, thats a formula and not VBA and i'm not good at converting some formulas to VBA. Also i've seen the "MID" function used, again either in formula form or used to retrieve a known length of characters from a string.

I'm sure i'll be embarassed at how simple this solution is. Thanks in advance - this forum has gotten me farther along in my project than i should be.

 

[pgc01]

Hi

Check the vba help. Left() and Right() are also vba functions.

 

[Mr_Ragweed]

Thanks for the quick reply. Doing exactly what you said as you responded. I recorded the formula as a macro and am trying to manipulate it accordingly now.

The formula version that works is: =Left(A2,24) & Right(A2,7)
...but i'm sure almost everyone already knew that

 

[Rick Rothstein]

You could also try this formula...

=REPLACE(A2,25,LEN(A2)-31,"")

 

[Mr_Ragweed]

Thanks Rick, but that's still a formula and i'm stuck in the VBA conversion of the formula I posted above.
[My Code Starts Here]

Sub AdjustCharacterLength()
'Keyboard shortcut:Ctrl+A
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim FinalRow As Long
Dim lngLoopCtr As Long
FinalRow = Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A" & FinalRow).Select
For lngLoopCtr = 1 To FinalRow
If Len(Cells(lngLoopCtr, "A")) > 31 Then
Cells(lngLoopCtr, "A").Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, ReplaceFormat:=False
End If
Next lngLoopCtr
For lngLoopCtr = 1 To FinalRow
If Len(Cells(lngLoopCtr, "A")) > 31 Then
'right here is where i'm needing my formula converted to code
End If
Next lngLoopCtr
End Sub
[End Code]
The first part of the loop works fine. i just need to put =Left(A2,24) & Right(A2,7) into the right language...

Thanks again.

 

[Mr_Ragweed]

an update if you will...

When i add this:

For lngLoopCtr = 1 To FinalRow
If Len(Cells(lngLoopCtr, "A")) > 31 Then
Cells(lngLoopCtr, "A").FormulaR1C1 = "=Left(RC,24) & Right(RC,7)"
End If
Next lngLoopCtr

The result is a "0" in the applicable cells.

So i'm wondering if i need to put this formula in a different cell for reference? (and then of course i'm gonna wind up asking how to reference it, but only after a good amount of trying on my own - i promise).

 

[Rick Rothstein]

Thanks Rick, but that's still a formula and i'm stuck in the VBA conversion of the formula I posted above.
[My Code Starts Here]

Sub AdjustCharacterLength()
'Keyboard shortcut:Ctrl+A
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim FinalRow As Long
Dim lngLoopCtr As Long
FinalRow = Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A" & FinalRow).Select
For lngLoopCtr = 1 To FinalRow
If Len(Cells(lngLoopCtr, "A")) > 31 Then
Cells(lngLoopCtr, "A").Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, ReplaceFormat:=False
End If
Next lngLoopCtr
For lngLoopCtr = 1 To FinalRow
If Len(Cells(lngLoopCtr, "A")) > 31 Then
'right here is where i'm needing my formula converted to code
End If
Next lngLoopCtr
End Sub
[End Code]
The first part of the loop works fine. i just need to put =Left(A2,24) & Right(A2,7) into the right language...

Thanks again.

I think you would use the formula you have, but replace each A2 with Cells(lngLoopCtr, "A").Address, but I think what you want can be done with less code. You are removing all spaces from the text in Column A and then keeping only the first 24 characters coupled with the last 7 characters for those cells whose text is longer than 31 characters, then give this macro a try..

Sub RemoveSpaceThenKeepFirst24AndLast7Characters()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEN(SUBSTITUTE(@,"" "",""""))>31,LEFT(SUBSTITUTE(@,"" "",""""),24)&" & _
                         "RIGHT(SUBSTITUTE(@,"" "",""""),7),SUBSTITUTE(@,"" "",""""))", "@", Addr))
End Sub

 

[Mr_Ragweed]

Wowsa!
Way cool, and thank you very much.
I got an error trying to insert my formula and change the "A2" to Cells(lngLoopCtr, "A").Address. It did not like the "A" part - 'expected end of statement error'. But that doesn't matter much because this:
Dim Addr As String
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(LEN(SUBSTITUTE(@,"" "",""""))>31,LEFT(SUBSTITUTE(@,"" "",""""),24)&" & _
"RIGHT(SUBSTITUTE(@,"" "",""""),7),SUBSTITUTE(@,"" "",""""))", "@", Addr))

was a trump card to the error statement .

Again thanks a million.

Not that you or any other reader really cares, but i started this VBA project 3 weeks ago and went from knowing only how to get to the editor to creating a project that now print outs at 20 pages. (creates PivotTables, alters PT's, many loops, creates userforms and msgboxes, and is all accessible via a 1 button click on the ribbon.). This stuff is way cool. I've learned a ton, learned a ton more from this forum and am actually a bit sad that my project is nearing completion. i'll probably have another question or 2, but it's not from lack of trying on my own, it's just trying to learn this language better.