[JAustin]

I'm working on programming my first VB userform / function. I openly admit, I barely know what I'm doing. But I'm working on a project and thought perhaps I could resolve an issue by writing an excel function in visual basic.

Can anyone explain how to write the code for a program that would help me seperate a column of alpha numeric data? (i.e. 123AP value is in column A. The program would generate value "123" into Column B and "AP" into Column C.

Any information is Greatly Appreciated!

Thanks,

Jesse

[weejas]

If you're certain that the string will always be numeric on one side and text on the other, it's fairly simple to write a custom function to do this.

Function SplitString(strStart As String, booSide As Boolean) As Variant
'Function to split a string composed of numeric and non-numeric characters
'booSide determines if the return value will be the numeric side (left, true) or the
'non-numeric side (right, false).

Dim strWorking as String   'Working variable
Dim strTemp as String    'Holder for return value

strWorking = strStart

If booSide Then

   'Parse strWorking from the left until you run out of numeric characters
   Do While IsNumeric(Left(strWorking, 1))

      'Add the character to the return variable
      strTemp = strTemp & Left(strWorking, 1)

      'Lop off that character from strWorking
      strWorking = Right(strWorking, Len(strWorking)-1)

   Loop

Else

   'Parse strWorking from the right until you reach numeric characters
   Do Until IsNumeric(Right(strWorking, 1))

      'Add the character to the return variable
      strTemp = Right(strWorking, 1) & strTemp

      'Lop that character from strWorking
      strWorking = Left(strWorking, Len(strWorking)-1)

   Loop

End If

'Populate the function with the return value

SplitString = IIf(booSide, CLng(strTemp), strTemp)

End Function

NB
I haven't tested this, but it looks about right. To use it, enter your value in A1, "=SplitString(A1, True)" into B1 and "=SplitString(A1, False)" into C1.

Good luck!