Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Macro for data cleaning

    Hi,

    I need to have a macro that removes the first all characters or digits from Column B to the Last Row where in like this are there.


    SS A/C 7657
    10102017
    111198760
    141478652
    ST 201981


    After Data Cleaning.
    7657
    2017
    98760
    78652
    201981

    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    How do you know what characters to remove if the entry is all numbers? Is it always the first 4 digits?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Paul for the reply.

    How do you know what characters to remove if the entry is all numbers?
    I Think, the best way would be the condition to remove any characters before numeric values in Column B will provide us the result.

    Is it always the first 4 digits?
    Yes and confirm only three type of 1010, 1111 & 1414

    Thanks

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Danny,

    I think this will do what you want. The code assumes that the list in column B starts on row 2

    In a standard module:
    Code:
    Public Sub FormatCol()
    Dim LastRow As Long, I As Long, s
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
        s = Split(Cells(I, 2), " ")
        If UBound(s) >= 1 Then
            Cells(I, 2) = s(UBound(s))
        ElseIf IsNumeric(Cells(I, 2)) Then
            Select Case Left(Cells(I, 2), 4)
                Case 1010, 1111, 1414
                    Cells(I, 2) = Mid(Cells(I, 2), 5, Len(Cells(I, 2)))
            End Select
        End If
    Next I
    End Sub
    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    danny69 (2016-01-07)

  6. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    I can see some regex being useful as a Find / Replace, but as Excel doesn't have that feature....

    cheers, Paul

  7. #6
    New Lounger
    Join Date
    Jan 2016
    Location
    Preston
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wouldn't a simpler solution be to sort the values. You would then have the three types in three groups?

  8. #7
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,
    Thanks active for alternate solution, but Maudibe macro solves my issue.

    Maudibe, one question for you, if there is no space between character & numeric OR if special character like / (AS/123456) then it does not delete it. Anyways to solve it.

    Thanks a lot.

  9. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    M's code splits the entry at a space, but if you don't have one you'd probably need to do it manually (in code).

    Note: I've not tested this!!!!

    cheers, Paul

    Code:
    Public Sub FormatCol()
    Dim LastRow As Long, I As Long, s
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
    	WhereToSplit = 1 'Set the split point at the beginning, we change it later if required
    	c = Cells(I, 2)
    	For J = Len(c) to 1 Step -1 'Work backwards through the cell contents to find the first number
    		if not IsNumeric(Mid(c, J, 1) Then WhereToSplit = J + 1 'You may need to remove the +1
    	Next
    	c = Mid(c, WhereToSplit) 'Collect only the numeric part
    	Select Case Left(c, 4)
    		Case 1010, 1111, 1414
    			Cells(I, 2) = Mid(c, 5)
    		Case Else
    			Cells(I, 2) = c
    	End Select
    Next I
    End Sub

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Danny,

    If Paul's code doesn't meet your needs, the added segments to my code will cycle through each character of the line and look for the first instance of a number using the ASCII code table. It will split the letters and/or special characters and return the number set.

    HTH,
    Maud

    Danny.png


    Code:
    Public Sub FormatCol()
    '-----------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, I As Long, s, t
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
    '-----------------------
    'IF SPACES, RETURN LAST SET
        s = Split(Cells(I, 2), " ")
        If UBound(s) >= 1 Then
            Cells(I, 2) = s(UBound(s))
    '-----------------------
    'IF ALL UBERS, REMOVE FIRST 4 NUMBERS IF MATCH CITERIA
        ElseIf IsNumeric(Cells(I, 2)) Then
            Select Case Left(Cells(I, 2), 4)
                Case 1010, 1111, 1414
                    Cells(I, 2) = Mid(Cells(I, 2), 5, Len(Cells(I, 2)))
            End Select
    '-----------------------
    'SPLIT BEFORE FIRST NUMBER IN STRING AND RETURN LAST SET
        Else:
            For J = 1 To Len(Cells(I, 2))
                Value = Asc(Mid(Cells(I, 2), J, 1))
                If Value >= 48 And Value <= 57 Then
                    t = Split(Cells(I, 2), Mid(Cells(I, 2), J - 1, 1))
                    Cells(I, 2) = t(UBound(t))
                    GoTo Nextval
                End If
            Next J
        End If
    Nextval:
    Next I
    End Sub

  11. #10
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Maud,
    Thanks.
    Your 2 script resolves my issues, but the format of numeric data is text. Right click->Format Cells->Category:Text.
    After removing the characters the data to be format as Number.

    One more question in this column B if there are 15 digits in the cell can we convert it to 8 digits, maintain 8 from right and else delete.
    001101066002134 <--15 digits converted to 8 -->66002134
    101111100023456 <--15 digits converted to 8 -->23456

  12. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    To force the numbers to be text add an apostrophe to the beginning.
    Add "'" & after the equals in the 3 Cells(I, 2) = lines. e.g. Cells(I, 2) = "'" & s(UBound(s))

    To convert to 8 digits add this on a new line before Next I. If Len(Cells(I, 2)) = 15 Then Cells(I, 2) = "'" & Right(Cells(I, 2), 8)

    cheers, Paul
    Last edited by Paul T; 2016-01-16 at 03:55.

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Hi Danny,

    Borrowing from Paul, the added code will take the 15 character value to 8 (blue). My preferred method to covert the vales to numbers would be the numberformat property (blue).

    HTH,
    Maud

    Code:
    Public Sub FormatCol()
    '-----------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, I As Long, s, t
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
    '-----------------------
    'IF SPACES, RETURN LAST SET
        s = Split(Cells(I, 2), " ")
        If UBound(s) >= 1 Then
            Cells(I, 2) = s(UBound(s))
    '-----------------------
    'IF 15 CHARACTERS, RETURN LAST 8
        ElseIf Len(Cells(I, 2)) = 15 Then
            Cells(I, 2) = Right(Cells(I, 2), 8)
    '-----------------------
    'IF ALL UBERS, REMOVE FIRST 4 NUMBERS IF MATCH CITERIA
        ElseIf IsNumeric(Cells(I, 2)) Then
            Select Case Left(Cells(I, 2), 4)
                Case 1010, 1111, 1414
                    Cells(I, 2) = Mid(Cells(I, 2), 5, Len(Cells(I, 2)))
                    'Cells(I, 2).NumberFormat = 0
            End Select
    '-----------------------
    'SPLIT BEFORE FIRST NUMBER IN STRING AND RETURN LAST SET
        Else:
            For J = 1 To Len(Cells(I, 2))
                Value = Asc(Mid(Cells(I, 2), J, 1))
                If Value >= 48 And Value <= 57 Then
                    t = Split(Cells(I, 2), Mid(Cells(I, 2), J - 1, 1))
                    Cells(I, 2) = t(UBound(t))
                    GoTo Nextval
                End If
            Next J
        End If
    Nextval:
        Cells(I, 2).NumberFormat = 0
    Next I
    End Sub

  14. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 984 Times in 914 Posts
    Oops, you want them as numbers, not text.

    cheers, Paul

  15. #14
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,
    Thanks Paul T one way or other i learnt something.

    Maudibe, i am still not getting the text convert to numeric, infact when i right click, format cells, category: Number(highlighted in blue) but aligned to left side of the cell, if i click inside the cell the cursor blinks and then i press enter it aligns to right side.

    Thanks for helping.

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Danny,

    No matter which condition in the code proves true, the second to the last line Cells(I, 2).NumberFormat = 0 is applied to the value. Select the entire column and format to right side (you will only need to do this once) then run the code.

    HTH,
    Maud

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    danny69 (2016-01-19)

Posting Permissions

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