Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Split Upper / Lower case + concatenate rows

  1. #1
    Junior Member
    Join Date
    Sep 2014
    Posts
    6
    Rep Power
    0

    Split Upper / Lower case + concatenate rows

    I'm looking for a solution for several issues I have with a copied data set.

    samset.jpg

    The data set is as per column A and B in above screenshot.
    The problem in column A is that it contains the unique ID number but also shows blanks. It's not containing always the same number of blanks until the next ID number.
    (I have approx 4000 ID's in the dataset).

    The problem in Column B is that it contains both upper case and lower case characters. I have found on this forum several ways how to extract those, but my problem is that I need to concatenate all lower cases together. In the end I need 2 seperate columns:
    Column D may contain only the upper case characters from column B and has to concatenate those from multiple cells in the column within the ID range (column A).
    Column E is simular, but here I should concatenate all lower case characters from multiple cells in column B, within the ID range (column A).

    Who would be able to help me with this?

    Thanks !!!

  2. #2
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    93
    Rep Power
    11
    Last edited by DocAElstein; 03-01-2024 at 03:04 PM.

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Code:
    Sub M_snb()
        ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas.Count, 1 To 2)
        y = 1
        
        For Each ar In Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
            c00 = Join(Application.Transpose(ar.Offset(-1, 1).Resize(ar.Rows.Count + 1)))
            For j = 1 To Len(c00)
               If Mid(c00, j, 1) Like "[a-z]" Then
                    sp(y, 1) = Left(c00, j - 1)
                    sp(y, 2) = Mid(c00, j)
                    y = y + 1
                    Exit For
                End If
            Next
        Next
        
        Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
    End Sub

  4. #4
    Junior Member
    Join Date
    Sep 2014
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by snb View Post
    Code:
    Sub M_snb()
        ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas.Count, 1 To 2)
        y = 1
        
        For Each ar In Cells(1).CurrentRegion.Columns(1).SpecialCells(4).Areas
            c00 = Join(Application.Transpose(ar.Offset(-1, 1).Resize(ar.Rows.Count + 1)))
            For j = 1 To Len(c00)
               If Mid(c00, j, 1) Like "[a-z]" Then
                    sp(y, 1) = Left(c00, j - 1)
                    sp(y, 2) = Mid(c00, j)
                    y = y + 1
                    Exit For
                End If
            Next
        Next
        
        Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
    End Sub
    thanks for the effort, but in my real data set I also have ID's without blank cells below and this code isn't picking those up.

  5. #5
    Junior Member
    Join Date
    Sep 2014
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by p45cal View Post
    see attached.
    super, this looks what I want to see.
    I'm new with this, and run into following issue:
    I copied my real dataset into column A and B and also added an extra number on the bottom.
    Then I change this part of the formula (2 parts) to the last cell number in my dataset. but then I get following error message:

    "You cannot change part of an array"

    how can I solve this?

  6. #6
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    93
    Rep Power
    11
    The arrays are only 2 cells wide. You select the two cells, adjust the formula, then ctrl+shift+Enter to enter the formula into both cells. Then you can copy the pair of cells down.

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Please do not quote !

    Why didn't you tell so in the first place ?

  8. #8
    Junior Member
    Join Date
    Sep 2014
    Posts
    6
    Rep Power
    0
    thanks p45cal

    @snb, my appologies, I thought it didn't make a difference.
    apperently seen the other solution, it doesn't.

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    It does; but you can only take it into account if the information doesn't lack.

    Code:
    Sub M_snb()
        ReDim sp(1 To Cells(1).CurrentRegion.Columns(1).SpecialCells(2).Count, 1 To 2)
        y = 1
        
        For Each cl In Cells(1).CurrentRegion.Columns(1).Offset(1).SpecialCells(2)
            c00 = cl.Offset(, 1)
            If cl.Offset(1) = "" Then c00 = Join(Application.Transpose(cl.Offset(, 1).Resize(cl.Resize(UBound(sp)).SpecialCells(4).Areas(1).Cells.Count + 1)))
            
            For j = 1 To Len(c00)
               If Mid(c00, j, 1) Like "[a-z]" Then
                    sp(y, 1) = Left(c00, j - 1)
                    sp(y, 2) = Mid(c00, j)
                    y = y + 1
                    Exit For
                End If
            Next
        Next
        
        Cells(20, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
    End Sub
    Last edited by snb; 09-21-2014 at 12:07 AM.

  10. #10
    Junior Member
    Join Date
    Sep 2014
    Posts
    6
    Rep Power
    0
    @SNB got an error running this code. but got the other one working in this thread, so thanks for your efforts!
    @p45cal: thanks! it worked, you saved me a week of work!!!! much appreciated.

Similar Threads

  1. Excel Macro to Split Multiple Columns into rows
    By cali-novice in forum Excel Help
    Replies: 2
    Last Post: 05-16-2014, 12:34 PM
  2. Replies: 6
    Last Post: 04-13-2014, 02:31 AM
  3. Random Time Between Fixed Lower And Upper Limits
    By papabill in forum Excel Help
    Replies: 9
    Last Post: 07-22-2013, 05:01 PM
  4. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  5. Reverse name in excel with upper case,edit formula
    By shrinivasmj in forum Excel Help
    Replies: 3
    Last Post: 09-11-2012, 01:31 PM

Tags for this Thread

Posting Permissions

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