Extract Text After Space In String

[holmbjerg]

I need to extract text after space in string e.g.

INV1019469 Intrum Justitia Oy
INV1028 Petskiboat Oy
INV102812 Palo, Tolvanen & Al
INV103 Fast Capital Oy

Result:
Intrum Justitia Oy
Petskiboat Oy
Palo, Tolvanen & Al
Fast Capital Oy

Thanks
Lasse

 

[VBABEGINER]

Thanks sir for reply.

Yes...That is the problem only. This all are in one column...and tried to work out something best...

What you have given earlier, that is super most....work for almost 95%. But this are some cases, where im creating different different code for every type. Now this above is remaining..my entire project will complete then...

I will share my code...if you want..

Can all of these formats you keep posting all be mixed together within the column at the same time?

 

[VBABEGINER]

Or else, Rick Sir..How do i create code for this..

EP 1051-CA (07 13)

For earlier examples Im using this..

'Format 1
    'Format = UTS-COVPG 7-03
    If b Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
[COLOR=#ff0000]        space = WorksheetFunction.Find(" ", b)[/COLOR]
        Cells(i, 3) = Left(b, space - 1)
        VrYY = Right(b, 2)
        VrMM = Left(Right(b, Len(b) - WorksheetFunction.Find(" ", b)), 1)
        Cells(i, 4) = VrMM & "/" & 1 & "/" & VrYY
    End If
    'Format = 17-02-5205 7-03
    If b Like "[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] [0-9]-[0-9][0-9]" Then
[COLOR=#ff0000]        space = WorksheetFunction.Find(" ", b)[/COLOR]
        Cells(i, 3) = Left(b, space - 1)

Now in the mentioned example, I have 2 space. How do i design code for this...Please revert..

Can all of these formats you keep posting all be mixed together within the column at the same time?

 

[Rick Rothstein]

Or else, Rick Sir..How do i create code for this..



For earlier examples Im using this..

'Format 1
    'Format = UTS-COVPG 7-03
    If b Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
[COLOR=#ff0000]        space = WorksheetFunction.Find(" ", b)[/COLOR]
        Cells(i, 3) = Left(b, space - 1)
        VrYY = Right(b, 2)
        VrMM = Left(Right(b, Len(b) - WorksheetFunction.Find(" ", b)), 1)
        Cells(i, 4) = VrMM & "/" & 1 & "/" & VrYY
    End If
    'Format = 17-02-5205 7-03
    If b Like "[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] [0-9]-[0-9][0-9]" Then
[COLOR=#ff0000]        space = WorksheetFunction.Find(" ", b)[/COLOR]
        Cells(i, 3) = Left(b, space - 1)

Now in the mentioned example, I have 2 space. How do i design code for this...Please revert..

I am not sure where you have your 2 spaces at (this forum collapses multiple space down to single spaces if that might apply to your posted example). Anyway, see how this macro does when used against all of your possible variations (I believe it correctly handles everything you have posted so far...

[table="width: 500"]
[tr]
	[td]Sub SplitLeadInTextFromTrailingDate()
  Dim R As Long, TempText As String, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    If Right(Data(R, 1), 1) = ")" Then
      Data(R, 1) = Application.Replace(Left(Data(R, 1), Len(Data(R, 1)) - 1), InStrRev(Data(R, 1), "(") - 1, 1, "")
      Data(R, 1) = Application.Replace(Data(R, 1), InStrRev(Data(R, 1), "("), 1, "")
    End If
    TempText = Replace(Replace(Replace(Data(R, 1), " ", ""), "/", ""), ")", "")
    If Mid(TempText, Len(TempText) - 2, 1) = "-" Then TempText = Application.Replace(TempText, Len(TempText) - 2, 1, "")
    If Not IsNumeric(Mid(TempText, Len(TempText) - 3, 1)) Then TempText = Application.Replace(TempText, Len(TempText) - 2, 0, 0)
    If Mid(TempText, Len(TempText) - 4, 1) = "-" Then TempText = Application.Replace(TempText, Len(TempText) - 4, 1, "")
    Result(R, 1) = Left(TempText, Len(TempText) - 4)
    Result(R, 2) = Format(Right(TempText, 4), "00""/01/""00")
  Next
  Range("B1:C" & UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]