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.

Free-bird Real Opportunities And Encyclopedias LLP = 50 Char 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. <tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Free-bird Real Opportunities And Encyclopedias LLP
2
3	Free-bird Real Opportunities And
4	Encyclopedias LLP

<tbody>
</tbody>

Sheet12

Worksheet Formulas Cell Formula A4 =TRIM(MID(A1,LEN(A3)+1,LEN(A1))) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A3 {=IF(LEN(A1)<38,A1,LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("2:38")),1)=" ",ROW(INDIRECT("1:37"))))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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