Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Streamline Code: use Trim?? (2003)

    Hi all,

    I have a file that gets imported from a CSV, and since the data is "raw" I have generated the below macro to cleanup some of the information. The Information is in column 'A', and in it's raw state looks like this:

    123456789,001,1
    122334455,002,1
    123532234,002,2
    etc,,,


    The first set, will be variable between 9 and 10 chars, the ",001,1" will be different between the comma's, and possibly the last digit.

    I need to remove the ",001,1" ( data from the string of data. I basically recorded a macro to get the data when it fits the ,001,1 but I am running into issues when the info changes. I am sure there is a more elegent method, but I do not quite understand the use of trim etc....



    Sub Deletesuffix()


    Sheets("CR Data").Select
    Cells.Select
    Columns("A:A").Select
    Selection.Replace What:=",001,1", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    You can use Date | Text to Columns with the Delimited option and comma as delimiter to distribute the data over columns A to C.
    You can record a macro and modify the recorded code (it'll use the TextToColumns method).

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    Ok, that just blew right by me.

    Are you talking about during the import? Because that would really messup my whole dataset/remaining macros.


    Really need to do this after the import without creating additional columns, that I would have to remove. Everything else is already automated to the point that it would really mess things up.


    Brad

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Streamline Code: use Trim?? (2003)

    You can do it in a temporary sheet.

    have the macro open the text file, creating the new sheet, parse the text, and then transfer anything to where you need it in the final location, then you can delete the temp sheet. This can allow you to use builtin routines instead of having to create all your own code.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    You can specify that you want to use only the first column. Here is an example:

    Range("A:A").TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True, _
    FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlSkipColumn), Array(3, xlSkipColumn))

    The FieldInfo array specifies that the first part is interpreted as General and that the second and third columns are skipped.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    Ok,
    so how does that fit into how I am opening the data?



    Sheets("CR Data").Select
    Range("A1").Select
    With Selection.QueryTable
    .Connection = _
    "TEXT;Cocuments and Settingsgz57l1My Documents"
    .TextFilePlatform = 437
    .TextFileStartRow = 6
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    End With

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    I see that you are specifying comma as delimiter. How come you get entries with commas in the first column? Were they enclosed in double quotes in the data file?

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    Hans,

    Yes, the data is exported from it's native system, and changing that method is not possible.

    I see that the comma delimited would normally parse the data and I could have handled that from the start.

    Brad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    So, can you change your code to parse the data?

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    I'll give it a shot....


    Brad

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    Ok,,,,

    I Imported the data, went through the parsing (selected comma and other with "). this really scrambled the data. I selected the columns to NOT import, and the end result was data not in the correct columns.

    After importing the data, isn't there an easier way to select the column, and trim the ",xxx,x" information?


    Brad

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    See <post:=691,597>post 691,597</post:> - you could run that instruction after importing the text file. Change Range("A:A") if needed.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Streamline Code: use Trim?? (2003)

    The formula =LEFT(A1,10) assumes that the first part will always be 10 characters long. In this thread, you stated that it can be 9 or 10, so you can't use the same formula.
    The code I posted in this thread should work for all lengths.

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Streamline Code: use Trim?? (2003)

    (Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

    Hans,

    After all that, I found the <post#=639488>post 639488</post#> (from me) asking basically the same thing with out the commas.

    Now, what I plan on is right after opening the file, create the "new column" but how do I find the end of the data, and insert the below formula into the new column? As the length of the data (# of rows) will change I cannot specifiy a certain cell to end at.

    Your reply is as follows:

    Say your data are in A1:A100
    In B1, enter the formula

    =LEFT(A1,10)

    Fill down to B100.
    With B1:B100 still selected, select Edit | Copy, then Edit | Paste Special > Values.
    This replaces the formulas with their values.
    You can now delete column A.

    Regards,
    Hans


    Thanks,
    Brad

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Streamline Code: use Trim?? (2003)

    I am jumping in very late here, but could this formula not do the job of truncating the values before the first comma:
    =LEFT(A1,FIND(",",A1)-1) - creates a text entry of the numbers, or,
    =LEFT(A1,FIND(",",A1)-1)+0 - converts the text result to a number again.
    It can also be added to VBA code that will automate the procedure as needed.
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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