Separating alphanumeric values

yackamd · #1 (**permalink**) 03-31-08, 12:04

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

savbill · #2 (**permalink**) 04-03-08, 22:20

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

yackamd · #3 (**permalink**) 04-06-08, 13:19

Thanks very much Bill for your help.

Regards
Davie

Internet Services

Web Development

Digital Marketing

Consumer Tech