Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Question Unanswered: Separating alphanumeric values

    Hi

    I am new to using VBA code in Excel and I have 2 queries relating to cleaning up the data in a spreadsheet which I am trying to solve.

    Is there a way to use or write an Exel function for the user to select a range of numeric and alphanumeric values in one column (eg, 1, 1A, 12, 12B, 39, 39C) and for every cell in the range split off the letter and then automatically place the two separate values (numeric and alpha) in 2 pre-selected cells.

    I was thinking of either designing a form in Excel, or using input boxes which would allow a user to select the range for the alphanumeric data to split and also the range for the split data to be placed in for each row in the range selected.

    i.e.
    Column
    1
    1A
    12
    12B
    39
    39C

    would be selected by the user and then they would select the range of two columns for the data to be split into 2 columns

    1
    1
    12
    39
    39

    and

    Null
    A
    Null
    B
    Null
    C

    The next question I have is it possible in Excel VBA to allow the user to select different ranges of data using an inpuit box or a form to pass to the function which can then use the data selected to find related values and then aggregate other cells in the range based on the related values?

    For example:

    if the spreadsheet range is:

    A1..D3

    and the columns have the following headings and data

    Column A[RoundNumber] B[StreetNumber] C[StreetName] D[NoItems]
    Line 1 - [1] [23] [High Street] [2]
    Line 2 - [1] [25] [High Street] [3]
    Line 3 - [1] [27] [High Street] [2]
    Line 4 - [2] [2] [Main Street] [1]
    Line 5 - [2] [4] [Main Street] [4]
    Line 6 - [2] [6] [Main Street] [1]


    I want the user to be able to select via a form or an input box any random range in the workbook such as A1..D3 and then select two ranges of related data to be sorted using their related values and also select a range of data to be concatenated based on the data selected and then a range of data to be added together for the related values.

    The function would need to identify all values in the range where there are unique values in A1..A6 and each unique value in C1..C6. Therefore for every row in the range where[RoundNumber] =1 and [StreetNumber] = "High Street" then the output to 2 cells further along the spreadsheet would be the concatenation of the value in [StreetNumber] i.e. "23, 25, 27" separated by a comma and the total of [NoItems] i.e. [7].

    Sorry if this is not very clear, as its hard to explain. I have attached an image of the input and desired output.

    Many thanks for any assistance in trying to crack these.

    Regards
    Davie
    Attached Thumbnails Attached Thumbnails Excel.JPG  

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Davie, You are asking 3 related question of one task which is realy adding an extra step and more complex requirement of your first question.

    Here's how you can parse out the charaters. I've used a function to do the work and another function to check for the correct selection arrangement. You would have to create a user form or create a button for the user to activate this process.
    Code:
    Sub RemoveCharacters()
      Call fRemoveChars
    End Sub
    
    Function fRemoveChars()
      Dim n As Integer
      Dim strNew As String, strNew2 As String
      Dim strVal As String
      Dim vCell As Range
      
      ' Make check to ensure correct selection
      If Not fAreasCheck() Then
        Exit Function
      End If
      
      n = 0
      For Each vCell In Selection.Areas(1)
       'Debug.Print vCell.Value
         strVal = vCell.Value
        strNew = Empty
        strNew2 = Empty
        For i = 1 To Len(vCell)
            strChr = Mid(strVal, i, 1)
            ' add any character you wish to exclude
            If Not strChr Like "[A-Z a-z # $ % & *]" Then
                strNew = strNew & strChr
            Else
                strNew2 = strNew2 & strChr
            End If
        Next
        vCell.Value = strNew
        Selection.Areas(2).Item(1).Offset(n, 0).Value = strNew2
        n = n + 1
        'Debug.Print strNew
      Next
      'fRemoveChars = strNew
    End Function
    
    ' Function checks for 2 selections in different columns
    ' Shows Alert message if selection is not correct
    Function fAreasCheck() As Boolean
    
      If Not Selection.Areas.Count = 2 Then
        msg = "Select 2 Columns for the source and target values"
        GoTo fAreasCheck_Fail
      End If
      
      If Selection.Areas(1).Column = Selection.Areas(2).Column Then
        msg = "Selections must be in 2 columns for source and traget values"
        GoTo fAreasCheck_Fail
      End If
    
      fAreasCheck = True
    fAreasCheck_Exit:
      Exit Function
    fAreasCheck_Fail:
      MsgBox msg, vbExclamation
      fAreasCheck = False
    
    End Function
    ~

    Bill

  3. #3
    Join Date
    Mar 2008
    Posts
    4
    Thanks very much Bill for your help.

    Regards
    Davie

Posting Permissions

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