Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008

    Unanswered: Back to basics anyone?

    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!



  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    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)
       '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)
    End If
    'Populate the function with the return value
    SplitString = IIf(booSide, CLng(strTemp), strTemp)
    End Function
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts