Error With Results Converting Text Date To Number

[Ark68]

I found this function through a Google search hoping to help me convert a text date (May 6, 2020) to a number that Excel can use.

Public Function GetDate1(ByVal rng As Range)
    GetDate = CDate(Left$(rng.Text, InStr(InStr(rng.Text, ", ") + 1, rng.Text, ", ") - 1))
End Function

With my example in cell A1 of my worksheet, the result I get when I enter (into another cell on the worksheet) =GetDate1(A1) is #VALUE!

Now I don't doubt it's an effective function, but I don't think I'm using it correctly. Unsure the purpose of the $ in the forumal. I suspect it needs to be tweaked somehow to recognize my value.
Suggestions?

 

[Fluff]

AFAIK it works on hidden workbooks, what is your code?

 

[Ark68]

I think I know my problem Fluff, but unsure how to efficiently overcome it ..
I have this structure causing me the issue I think ...

Sub
    With workbook1.worksheet1
           open and hide new workbook2 (wb_rmr1)
           With wb_rmr1.worksheet1
                  Apply TextToColumns and other worksheet (wb_rmr.worksheet1) changes
           End With
    End With
End Sub

I don't think I can nest like that. I'm working through that understanding. I figure if I got rid of that nesting I'd be ok.

 

[Fluff]

Try it like

 With wb_rmr1.Sheets("sheet1")
      .Range("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
         Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
         :=Array(1, 3), TrailingMinusNumbers:=True
   End With

 

[Ark68]

Fluff, that works.
Thanks. This thread was chocked full of teachings.

 

[Fluff]

Glad we could help & thanks for the feedback