How do i search for a caridge return line feed

Posted on 2004-10-25
Last Modified: 2009-07-29
i am copying data from excel say 10 cels of data,
i am then taking the clipboard into a variable,
i need to write a function to take the values and put them
into seperate variables, but the values are seperated by a little square,
which is a caridge return,


Question by:CraigLazar
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    There is the split function,which splits a string to a string array using a specific delimeter.To be more specific i will give you an example.

    Lets say you have a sentence ,ex. "Hello my dear friend"

    If you write this code

    dim mystring as string
    dim myarray() as string

    mystring = "Hello my dear friend"

    myarray=split(mystring," ") 'Which means that you separate each word and the dilimeter is a blank " "

    After executing this you will have the following array


    If you need to have carriage return as the dilimeter use it like this


    LVL 4

    Author Comment

    sorry did not work
    1      2      5      4      5      6      7      8      9      10

    here is the data i am working with from the clip board - when i paste it into my text box,
    i get little pipe symbols, i need to be able to read the values between the pipes, i tried the replace functiion aswell as vbcr,cbcrlf,vblf - none of them picked them up
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    Then propably it is not one of those characters.What you can do is using the following sub

    Private Sub CharSet(str As String)
    For i = 1 To Len(str)
    Debug.Print Chr(Asc(Mid(str, i, 1))), "-", Asc(Mid(str, i, 1))
    End Sub

    When you pass the clipboard to a variable

    call this sub like this

    CharSet yourClipBoardVariable

    With this sub you will know what is the ascii code of the dilimeter character.
    Then you may delete the sub and its reference and use


    where mycharracter code is the code you will have seen on the immidiate window
    LVL 23

    Expert Comment

    by:Ioannis Paraskevopoulos
    Or you may copy paste the results here so i can help you.

    LVL 52

    Expert Comment

    by:Carl Tawn
    To split on a carriage return you can use the VB constant vbCr.


        myArray = Split(sYourString, vbCr)

    Or, if seperated by a Carriage Return plus a Linefeed then use vbCrLf instead of vbCr.

    Hope this helps.
    LVL 4

    Author Comment

    thanks for all the help
    this is what was in my debug window
    (in excell i just had 1 2 3 in 3 cells, i then copied that to the clipboard
    1             -              49
                  -              9
    2             -              50
                  -              9
    3             -              51

    from here could it be the -        9 val ?

    LVL 23

    Accepted Solution

    9 is the code for tab.So you may put vbtab instead of vbcr.

    But then you must be sure that alwayw a tab is the dilimeter,or else you won't get the right results.
    LVL 16

    Expert Comment

    I expect that Excel copies cells to the clipboard as tab delimeted.  If so, using the above code:

          myArray = Split(sYourString, vbTab)

    should do it for you...


    LVL 4

    Author Comment

    hey thanks so mmuch , worked like a peach
    daved my bacon there :)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    856 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

    18 Experts available now in Live!

    Get 1:1 Help Now