Solved

trim the first 2 characters from each line in a file

Posted on 2010-11-10
11
670 Views
Last Modified: 2012-05-10
I have 4 types of files I receive which are outputs from a source I have no control over. Each file has in common that there are a consistent 2-4 characters in front of each line (in the 1st column for XLS docs). I want a scripted way to remove these. Can you suggest a method?
Thank you!
JohnD
0
Comment
Question by:johndarby
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 24

Accepted Solution

by:
broomee9 earned 250 total points
ID: 34106244
Assuming headers, this will remove the first 2 characters from every cell in column A.

Option Explicit

Sub Remove2Chars()

    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
        Cells(i, 1).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 2)
    Next i

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 34106265
To use a formula approach, you could put this in B2 and drag down to remove the first two characters in A2 and down:

=RIGHT(A2,LEN(A2)-2)


Also, you say between 2-4 characters.  What is the logic behind removing 2 or 4?
0
 
LVL 3

Assisted Solution

by:fredniel
fredniel earned 250 total points
ID: 34106281
Hi,
Can you post an example... or be more specific.

to figure out if there will be some delimitators or something.
How can we differentiate each type of file?

if your are trying just to remove the first column:

Range("A").EntireColumn.Delete



0
 
LVL 1

Author Comment

by:johndarby
ID: 34106495
Thanks folks! I don't have an example for you atm, but the extra characters seem to be random ASCII which precede the contents of each line in the docs. Usually it is 2 characters, sometimes 4 and occasionally it will be 5-9 characters, including one or more spaces, between the "garbage" characters.

The docs come from text dumps (they call them reports) from an MVS app called Universe, where one of the conversions that happens is EBCDIC-->ASCII. It's a black box to me.
0
 
LVL 3

Expert Comment

by:fredniel
ID: 34106551
i think that we really need you to prepare an example...
there must be something to figure out how many characters to delete...

How can we identify those "garbage" characters...

there is something missing in the formulation of this problem.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:johndarby
ID: 34106885
I can get you an example...I just need to strip customer-specific data, first since this has payroll info in it. :)
0
 
LVL 3

Expert Comment

by:fredniel
ID: 34106927
Agree with that. We are waiting for you then.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34110730
Here is a code to select an Excel file and replace string bits from the 1st column. Note, multi-select Excel file is possible as well as the string bits (for cleaning) and Column Index (data column) is configurable.

To run the code in the attached file, press CNTRL + SHIFT + M
Option Explicit
Private Const sJunk As String = "#$%!"
Dim sJunkFormula As String

Public Sub CleanFileData()
Dim oOut, oWB As Workbook, nCtr As Integer
On Error Resume Next
oOut = Application.GetOpenFilename("Excel Files, *.xl*;*.xls;*.xlt", 2, "Select the files to clean", , True)
If Not IsArray(oOut) Then GoTo ErrCancel
sJunkFormula = ""
For nCtr = 1 To UBound(oOut)
    Set oWB = Application.Workbooks.Open(oOut(nCtr))
    CleanRange oWB.Sheets(1).Range("A:A")
    oWB.Save
    oWB.Close
    Set oWB = Nothing
Next
Exit Sub
ErrCancel:
MsgBox "No File was selected.", vbCritical
End Sub

Public Sub CleanRange(oRange As Range)
Dim nCtr As Integer, oCell As Range
Dim m_sJunkFormula As String

If sJunkFormula = "" Then
    sJunkFormula = """~|~"""
    For nCtr = 1 To Len(sJunk)
        sJunkFormula = "SUBSTITUTE(" & sJunkFormula & ",""" & Mid(sJunk, nCtr, 1) & ""","""")"
    Next
End If
Set oRange = Application.Intersect(oRange, oRange.Worksheet.UsedRange)
oRange.NumberFormat = "General"
'oCell.Formula = "=" & Replace(sJunkFormula, "~|~", Replace(oRange.Cells(1).Address, "$", ""))
For Each oCell In oRange.Cells
    m_sJunkFormula = "=" & Replace(sJunkFormula, "~|~", oCell.Value)
    oCell.Value = Application.Evaluate(m_sJunkFormula)
Next
End Sub

Open in new window

CleanFiles.xlsm
0
 
LVL 33

Expert Comment

by:Norie
ID: 34111930
John
If you can open these files in Excel and all you want to do is remove the first 2 characters of each line you could use Data>Text to columns... with fixed width.
You would only need one break line after the characters you don't need and you can chose not to import that column on the 3rd step.
If it isn't going to be the first 2 characters each time then you could create code that does the text to columns for however many characters it is.
Here's the code for 2 characters.
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(2, 1)), TrailingMinusNumbers:=True
The Array... part breaks down to this.
Array(0,9) - 1st column, do not import
Array(2,1) -2nd column, import starting at character 2
Note the character position starts at 0.
So for 3 characters you would change Array(2,1) to Array(3,1), for 4 Array(4,1)... and so on.
This would work if you can open the file in Excel and determine the no of characters to ignore.
 
0
 
LVL 1

Author Comment

by:johndarby
ID: 34112970
Guys, I am having a hard time getting you a sample file. The problem lies with how much data (SSN, names, addresses, policy number...) is sensitive. I think I just have to be thnkful for the help you've given already. Thank you so much!
JohnD
0
 
LVL 1

Author Closing Comment

by:johndarby
ID: 34112985
Thank you again!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now