How to split hypen from text in Access 2010

[HC11112]

Btw JHB, just curious to ask...instead of splitting the code and updating into a new table? Possible can we split the code and update into the same existing table?

example
LPN COLUMN A COLUMN B COLUMN C
AEXF0.5 BK
SSX 0.85 BK
SSF 0.5 OR/BK

 

[JHB]

Change the code to the below, (remember to add the fields in table).

  Dim dbs As DAO.Database, rst As DAO.Recordset, splString() As String
  Dim x As Integer, y As Integer, InsertString As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("TheDataTable")
  If Not rst.EOF Then
    Do
      If InStr(1, rst![Original_Data_Field], " ") Then
        rst.Edit
        rst![Column_A] = Left(rst![Original_Data_Field], InStr(1, rst![Original_Data_Field], " ") - 1)
        rst![Column_B] = Right(rst![Original_Data_Field], Len(rst![Original_Data_Field]) - InStr(1, rst![Original_Data_Field], " "))
      Else
        splString() = Split(rst![Original_Data_Field], "-")
        For x = 0 To UBound(splString)
          If IsNumeric(splString(x)) Then
            InsertString = ""
            For y = 0 To x - 1
              InsertString = InsertString & splString(y) & "-"
            Next y
            rst.Edit
            rst![Column_A] = Left(InsertString, Len(InsertString) - 1)
            rst![Column_B] = splString(x)
            InsertString = ""
            For y = x + 1 To UBound(splString)
              InsertString = InsertString & splString(y) & "-"
            Next y
            rst![Column_C] = Left(InsertString, Len(InsertString) - 1)
          End If
        Next x
      End If
      rst.Update
      rst.MoveNext
    Loop Until rst.EOF
  End If