Remove everything after second last space

[Deepk]

hi all,

I need a macro for removing everything after second last space from a string. example data is provided below.

My name is khan.
this is not an obvious mistake.
could you please come here

after running macro it should be like:

My name
this is not an
could you please

Please not that formula is not required.

 

[oldbrewer]

My name is khan.

this is not an obvious mistake.

could you please come here

after running macro it should be like:

My name

this is not an

could you please

this macro does it

Sub Macro4()

'

' Macro4 Macro

' Macro recorded 04/01/2019 by bob

'

'

Dim space(50)

My name

rrow = 19

this is not an

For j = 1 To 3

could you please

For k = 1 To 99

mylen = Len(Cells(j, 1))

If Mid(Cells(j, 1), k, 1) = " " Then Sum = Sum + 1: space(Sum) = k

If k = mylen Then GoTo 50 Else GoTo 100

50 critspace = space(Sum - 1)

rrow = rrow + 1

Cells(rrow, 1) = Mid(Cells(j, 1), 1, critspace)

100 Next k

Sum = 0

Next j

End Sub

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>

 

[Fluff]

Another option

Sub Deepk()
   Dim Cl As Range
   Dim Splt As Variant
   Dim i As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Splt = Split(Cl.Value)
      For i = 0 To UBound(Splt) - 2
         Cl.Offset(, 1).Value = Trim(Cl.Offset(, 1).Value & " " & Splt(i))
      Next i
   Next Cl
End Sub

 

[Deepk]

Thank you friends. both solutions are working

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Deepk]

Glad we could help & thanks for the feedback

hi Fluff,

would it be possible that changes occur in same column not in the adjacent column.

Look forward hearing from you. Thank you.

 

[Fluff]

Try

Sub Deepk()
   Dim Cl As Range
   Dim Splt As Variant
   Dim i As Long
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Splt = Split(Cl.Value)
      Cl.ClearContents
      For i = 0 To UBound(Splt) - 2
         Cl.Value = Trim(Cl.Value & " " & Splt(i))
      Next i
   Next Cl
End Sub