Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    How To Separate Text And Numbers From A String

    Hi All,

    lately I get Stock-Data from various sources by E-mail in the following format:
    GRAPHITE ONE RESOURCES0,051+6,25 % - MOLYCORP0,365+18,89 % - GRAPHENE NANOCHEM0,472+0,64 % and so on.

    Then I import that data into Excel and would like to have the format as shown in my attached Sample.

    Please note that there should be a Blank before the Plus-sign.

    Thank you very much in advance for your time and help.

    Friendly regards,

    Wolfgang

    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi WG,

    It wasn't very clear what you wanted so I posted 2 solutions. Are you looking to do something like these?

    wg4.png

    Solution 1 in a standard module:
    Code:
    Public Sub SplitStock1()
    For H = 2 To 21
        t = Split(Cells(H, 1), ",")
        s = Split(Cells(H, 2), ",")
        Cells(H, 4) = Left(t(0), Len(t(0)) - 1)
        Cells(H, 6) = Left(s(0), Len(s(0)) - 1)
        t(1) = "0," & t(1)
        s(1) = "0," & s(1)
        u = Replace(t(1), "+", " +", 1, 1)
        v = Replace(s(1), "-", " -", 1, 1)
        Cells(H, 5) = u & "," & t(2)
        Cells(H, 7) = v & "," & s(2)
    Next H
    End Sub
    If solution 1 is what you need, I will condense the drafted code

    Solution2 in a standard module:
    Code:
    Public Sub SplitStock()
    Row = 24
    s = Split(Cells(24, 1), "-")
    For I = 0 To UBound(s)
        t = Split(s(I), ",")
        Cells(Row, 4) = Left(t(J), Len(t(J)) - 1)
        t(1) = "0," & t(1)
        u = Replace(t(1), "+", " +", 1, 1)
        Cells(Row, 5) = u & "," & t(2)
        Row = Row + 1
    Next I
    End Sub
    If you could post a workbook with one sheet (sheet1) of your raw imported data and Sheet2 of how that exact data should result, that would enable us to code exactly what you need. As of now, it is a bit unclear how many stocks will be in the line. Your sample line in your post has 3 stocks in the line separated by hyphens whereas your sample spreadsheet doesn't match.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-06-06 at 10:17.

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

    wolfgang (2015-06-06)

  4. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    both solutions are just fine and I thank you very much for your kind help.

    The data in my sample are the actual data but their order differ every minute.

    At the end of each day I get this summary by E-mail.

    Again, thank you VERY MUCH and have a nice weekend.

    All the best,

    Wolfgang

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    WG,

    If you need help integrating it into your workbook, let me know.

    Maud

  6. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    I really appreciate your kind offer but the E-mail data are just for info to summarize the daily actions.

    Friendly regards,

    Wolfgang

Posting Permissions

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