Below is the code (adapted from Craig's example) and it does exactly what I
need it to! If my coding looks more complicated than it needs to be, feel
free to tell me!


Sub ObtainAcNumber()

Dim strCellValue, strReference, strAcNum, strChr As String
Dim intRow, intCol, x As Integer

intRow = 2

Do While Cells(intRow, 1).Value <> "" ''' Loops through each cell  that
contains data

strCellValue = Cells(intRow, 7).Value

For x = 1 To Len(strCellValue) ''' Loops through each character in the
cell

strChr = Mid(strCellValue, x, 1)

If IsNumeric(strChr) Or strChr = "-" Then

''' Checks the length and format  of the numerical  string each
time a number is added

If (Left(strAcNum, 1) = "0" And Len(strAcNum) < 9) Or _
''' Format "000000000"

(Left(strAcNum, 1) <> "0" And Len(strAcNum) < 8) Or _
''' Format "00000000"

(InStr(strAcNum, "-") > 0 And Left(strAcNum, 1) = "0" And _
Len(strAcNum) < 11) Or _ ''' Format "00-000000-0" (not
exact, but sufficient)

(InStr(strAcNum, "-") > 0 And Left(strAcNum, 1) <> "0" And _
Len(strAcNum) < 10) Then ''' Format "0-000000-0" (not
exact, but sufficient)

strAcNum = strAcNum & strChr

If x = Len(strCellValue) And Len(strAcNum) < 8 Then
strAcNum = ""

End If

Else

''' If the numerical string has finished and is not at least 8
numbers in length, it is reset to ""

If strAcNum <> "" Then

If Len(strAcNum) < 8 Then strAcNum = ""

End If

strReference = strReference & strChr

End If

Next x

''' Removes the zero at the beginning of the numerical string, if
applicable

If Left(strAcNum, 1) = "0" Then strAcNum = Right(strAcNum, Len(strAcNum)
- 1)

If strAcNum <> "" Then
Cells(intRow, 6).Value = strAcNum
Cells(intRow,7).Value = strReference
End If

strReference = "": strAcNum = ""

intRow = intRow + 1

Loop

End Sub