Extract data from text string

[Nubie2019]

"Mon, Mar. 11, 2018ABC, FGHO117 Comments"

I want to extract from the above text string, "Mon, Mar. 11, 2018" in one cell, and "ABC, FGH" in another cell.

Is it possible without using VBA?

Thanks

 

[XOR LX]

Hi,

It would be useful to see a few more examples so that we can be sure that a given formula will work for all cases.

Regards

 

[AlKey]

Assuming the text string is in cell A1
1. Enter formula in B1
=LEFT(A1,18)

2.Enter in C1
=LEFT(SUBSTITUTE(A1,B1,""),8)

 

[Nubie2019]

Hi, few more examples,

"Thu, Feb 16GGHL, FGJK, LIM2 Comments"

"Feb 2, 2018FGH, IJK, LK9 comments"

In every case, I need the date section extracted and the upper case symbols in the middle. I need the date and the symbols in two seperate cells.

Thanks

 

[Nubie2019]

Hi Alley, the text string will not always be the same length, I have provided a few more examples below in response to XOR LX's request.

 

[Rick Rothstein]

Hi Alley, the text string will not always be the same length, I have provided a few more examples below in response to XOR LX's request.

It is worse than that... not only are they different lengths, but their construction is different... years or no years, day of week or no day of week, period after the month or no period after the month, etc.

Hi, few more examples,

"Thu, Feb 16GGHL, FGJK, LIM2 Comments"

"Feb 2, 2018FGH, IJK, LK9 comments"

In every case, I need the date section extracted and the upper case symbols in the middle. I need the date and the symbols in two seperate cells.

These should have been in your first message as your original example was not representative of what you have now posted.

See if these UDF's (user defined functions) do what you want...

Function GetDate(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) - 1
    If Mid(S, X, 2) Like "#[A-Z]" Then
      GetDate = Left(S, X)
      Exit For
    End If
  Next
End Function

Function GetLetters(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) - 1
    If Mid(S, X, 2) Like "[A-Z][A-Z]" Then
      GetLetters = Mid(S, X)
      Exit For
    End If
  Next
  For X = 1 To Len(GetLetters) - 1
    If Mid(GetLetters, X, 2) Like "[A-Z][!A-Z ,]" Then
      GetLetters = Left(GetLetters, X)
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate and GetLetters just like they was a built-in Excel functions. For example,

=GetDate(A1)

=GetLetters(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Nubie2019]

Thanks Rick! Yes, the UDFs worked like a charm.