# 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.
Split.xlsx
###### Who is Participating?

x
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.

Commented:
You'll need to use a reference like this to fins the non-numeric characters
http://www.eileenslounge.com/viewtopic.php?f=27&t=5709
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-finding-the-position-of-the-first-integer-in-an-alphanumeric-string/

Then you will want to use FIND, LEFT, RIGHT and MID.
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:

=SplitDocketNumber(B4)

Press CTRL+SHIFT+ENTER to enter the formula.

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

See attached workbook.

Kevin
Split.xlsm

Experts Exchange Solution brought to you by