Split text in column using second space from left

[Leonardo]

Is there a convenient way to split text in to two columns using the second space from the left. The text is not fixed length. There are no potential delimiters other than space characters.
I am using Excel 2016 on a Windows 10.
Thank you

 

[silentwolf]

Hi Leonardo,

https://support.office.com/en-us/ar...s-Wizard-30B14928-5550-41F5-97CA-7A3E9C363ED7


Or you can use those two UDF

Function SplitText(ByVal strText As String) As String
    Dim intPos As Integer
    
    intPos = InStrRev(strText, " ")
    SplitText = Mid(strText, intPos + 1)
    
End Function

Function GetFirstPartString(ByVal strText As String) As String
    Dim intPos As Integer
    
    intPos = InStrRev(strText, " ")
    GetFirstPartString = Left(strText, intPos - 1)
    
End Function

HTH

 

[sandy666]

you can try PowerQuery aka Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([raw data], " ", 1), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([raw data], " ", 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"raw data"})
in
    #"Removed Columns"[/SIZE]

 

[Fluff]

Another option

Sub leonardo()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
      On Error Resume Next
      Cl.Offset(, 1) = Trim(Mid(Cl, InStr(InStr(1, Cl, " ") + 1, Cl, " ")))
      On Error GoTo 0
      Cl = Trim(Replace(Cl, Cl.Offset(, 1), ""))
   Next Cl
End Sub

 

[Fluff]

Or without a loop

Sub Leonardo2()
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      .Offset(, 1).Value = Evaluate(Replace("iferror(right(@,len(@)-find(""|"",substitute(@,"" "",""|"",2))),"""")", "@", .Address))
      .Value = Evaluate("if({1},substitute(" & .Address & "," & .Offset(, 1).Address & ",""""))")
   End With
End Sub

 

[Leonardo]

Thank you Silentwolf, sandy66 and Fluff for your replies. I'm only marginally familiar with macros and I needed to get this worksheet for tomorrow morning. I did a brute force parse using text to columns and FlashFill. That is not adequate for the long term and I will review your macros and see if I can figure out how to implement one of them. I'm sure I will have more questions as I try to work with the macros.

Thanks again,

Leonardo

Hi Leonardo,

https://support.office.com/en-us/ar...s-Wizard-30B14928-5550-41F5-97CA-7A3E9C363ED7


Or you can use those two UDF

Function SplitText(ByVal strText As String) As String
    Dim intPos As Integer
    
    intPos = InStrRev(strText, " ")
    SplitText = Mid(strText, intPos + 1)
    
End Function

Function GetFirstPartString(ByVal strText As String) As String
    Dim intPos As Integer
    
    intPos = InStrRev(strText, " ")
    GetFirstPartString = Left(strText, intPos - 1)
    
End Function

HTH

 

[Rick Rothstein]

Or without a loop

Sub Leonardo2()
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      .Offset(, 1).Value = Evaluate(Replace("iferror(right(@,len(@)-find(""|"",substitute(@,"" "",""|"",2))),"""")", "@", .Address))
      .Value = Evaluate("if({1},substitute(" & .Address & "," & .Offset(, 1).Address & ",""""))")
   End With
End Sub

Here is another way to write a loopless macro for this question...

[table="width: 500"]
[tr]
	[td]Sub Leonardo2()
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("IF({1},SUBSTITUTE(" & .Address & ","" "",CHAR(1),2))")
    .TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]