How to find and truncate text after the first space before the 38th character

[Seric]

I am trying to write either a formula or Macro to find the last space in a string before the 38th character. My problem is that my data is not standard and I don't want to truncate in the middle of a word that falls at 37 characters. See example below.

[TABLE="width: 438"]
<tbody>[TR]
[TD="width: 438"]Free-bird Real Opportunities And Encyclopedias LLP = 50 Char

[TABLE="width: 438"]
<tbody>[TR]
[TD="class: xl65, width: 438"]Free-bird Real Opportunities And Ency = 37 char truncation

The goal would look like the truncation below where Encyclopedias is not cut in the middle.

Goal = Free-bird Real Opportunities And

Subsequently I will need to pull out the remaining portion of the name (Encyclopedias LLP) into a different cell.

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Seric()
   Dim a As Long
   Dim V As String
   V = Range("J2").Value
   a = InStrRev(V, " ", 38)
   Range("K2") = Left(V, a - 1)
   Range("L2") = Right(V, Len(V) - a)
End Sub

Change ranges to suit

 

[Eric W]

Or with formulas:

	A
Free-bird Real Opportunities And Encyclopedias LLP
Free-bird Real Opportunities And
Encyclopedias LLP

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet12


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=TRIM(MID(A1,LEN(A3)+1,LEN(A1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]{=IF(LEN(A1)<38,A1,LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("2:38")),1)=" ",ROW(INDIRECT("1:37"))))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Seric]

This is FABULOUS, thank you!

I modified my code to find the values in the entire column that I need to analyse, but the debugger is getting caught by the cell's who's length is less that 38 already. Suggestions?

Sub Seric()
Dim a As Long
Dim V As String

For i = 2 To 161

V = Cells(i, "B").Value
a = InStrRev(V, " ", 38)

Cells(i, "C") = Left(V, a - 1)
Cells(i, "D") = Right(V, Len(V) - a)

Next i
End Sub

 

[Seric]

This also worked, many thanks!!

 

[Fluff]

How about

Sub Seric()
   Dim a As Long, i As Long
   Dim V As String
   
   For i = 2 To 161
      If Len(Cells(i, "B")) < 39 Then
         Cells(i, "C") = Cells(i, "B")
      Else
         V = Cells(i, "B").Value
         a = InStrRev(V, " ", 38)
         
         Cells(i, "C") = Left(V, a - 1)
         Cells(i, "D") = Right(V, Len(V) - a)
      End If
   Next i
End Sub