Split Excel Cell with no uniform delimiter

Hello Experts!

I'm having a bit of trouble figuring out a formula to split the contents of cells that don't follow a uniform delimiter format. I have attached an excel spreadsheet with the data i'm trying to separate for reference. I need to split the "DocketNo" into three separate cells. in the column "DocketNo" The first set of numbers is the Docket number, the second set of numbers is the Sale number and the third set is the temperature.

the delimiters range from spaces, commas, full stops and forward slash

Can anyone help me with a formula that will help me split this data with minimal manual data manipulation!?

Thank you to anyone who can help.
mickferrariICT TechnicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carol ChisholmCommented:
You'll need to use a reference like this to fins the non-numeric characters

Then you will want to use FIND, LEFT, RIGHT and MID.
zorvek (Kevin Jones)ConsultantCommented:
Place this UDF in a code module:

Public Function SplitDocketNumber(ByVal Text As String) As Variant

    Dim Result As Variant
    Dim Position As Long
    Dim TokenNumber As Long
    Dim TokenPosition As Long
    Dim InToken As Boolean
    Dim InDelimiter As Boolean
    Result = Array("", "", "")
    InDelimiter = True
    For Position = 1 To Len(Text)
        If InDelimiter Then
            If Mid(Text, Position, 1) Like "#" Then
                TokenPosition = Position
                TokenNumber = TokenNumber + 1
                If TokenNumber = 3 Then
                    Result(TokenNumber - 1) = Mid(Text, TokenPosition, Len(Text) - TokenPosition + 1)
                    SplitDocketNumber = Result
                    Exit Function
                End If
                InToken = True
                InDelimiter = False
            End If
        End If
        If InToken Then
            If Not Mid(Text, Position, 1) Like "#" Then
                Result(TokenNumber - 1) = Mid(Text, TokenPosition, Position - TokenPosition)
                InDelimiter = True
                InToken = False
            End If
        End If
    Next Position

End Function

To use it, select cells C4:E4 and enter this formula:


Press CTRL+SHIFT+ENTER to enter the formula.

Copy cell C4:E4 to the end of the list.

See attached workbook.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mickferrariICT TechnicianAuthor Commented:
Perfect. That worked exactly how i wanted.

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.