Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Extract Data Between Spaces
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Data Between Spaces

    Hi All

    May I ask the board if they could help me with some formula to extract the data below "Data Source" and put in the the corresponding cells as shown below. I think it is by way of breaking down the data between the spaces but I am unable to work it out myself. The data is consistent in that it appears in three blocks separated by two spaces if that is of any assistance.Many thanks in advance.

    Kind Regards


    Code:
    DATA SOURCE REQUIRED DATA
    7GF C5Hc 4K 7 GF C5 Hc 6
    7Gd C43yHc 6K 7 GD C4 3y Hc 6
    9St C5Md 21K 9 ST C5 Md 21
    7St/Slw C43yHc 4K 7 St/Slw C4 3y Hc 4
    12St/Fs C5Md 2K 12 St/Fs C5 Md 2
    16Y/Sft HcH 815K 16 Y/Sft HcH 815
    17Sft Ch 4K 17 Sft Ch 4
    24Gd HcCh 5K 24 Gd HcCh 5
    22Hy NvChG3 23K 22 Hy NvCh G3 23
    7GS C12yG3 22K 7 GS C1 2Y G3 22

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,763
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Of the source data, I think I can see the 1st block is separated into character and numeric portions and put into the 1st and second column of the results. I also think I can see that the 3rd block simply has the K removed and the number is placed in the 7th column. But there must be rules/restrictions on how to split up the middle block and where to put each bit of that midle block into one of 4 result columns - for example, it may be that in the first of the 4 columns you can only have a C followed by a single digit, in the second of the 4 columns you can only have a single digit followed by a y. In the 3rd of the 4 columns it may be that there can only be a limited range of letters allowed.
    So more info needed.
    It's likely the solution will involve a macro.

  3. #3
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Quote Originally Posted by p45cal View Post
    Of the source data, I think I can see the 1st block is separated into character and numeric portions and put into the 1st and second column of the results. I also think I can see that the 3rd block simply has the K removed and the number is placed in the 7th column. But there must be rules/restrictions on how to split up the middle block and where to put each bit of that midle block into one of 4 result columns - for example, it may be that in the first of the 4 columns you can only have a C followed by a single digit, in the second of the 4 columns you can only have a single digit followed by a y. In the 3rd of the 4 columns it may be that there can only be a limited range of letters allowed.
    So more info needed.
    It's likely the solution will involve a macro.
    Hi p45cal

    Yes you are correct in your assumptions however I am not sure if I will be able to explain the middle block but will give it a go. If after the C4 OR C5 there is a 2y, 3y or 4y then I need that separating out also as shown in the eg. So in effect I need any nos after the C to be extracted and placed with the C in shown column, the numbers attached to C will only ever go up to 7 and the 2y,3y 0r 4y placed in shown column also. Hope that helps a little.

    Kind Regards

  4. #4
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Hi All

    Hoping the further explanation above may help in my receiving a solution to my problem.

    Kind Regards

  5. #5
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,763
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    I need to know a little more about the 2y, 3y, 4y etc.; always a single digit before the y, anything else apart from a y possible? only ever 2 characters in total?
    Also similar information around constraints for G3, G?
    Last edited by p45cal; Sep 14th, 2012 at 07:25 AM.

  6. #6
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Hi p45cal

    Many thanks for replying it is much appreciated.

    Yes always a single digit before the y nothing else possible bar the y, and only those two characters (ie 2y,3y or 4y)
    The same applies for G3 although the number could be either 1,2 or 3. (ie G1,G2 or G3)


    I hope that is a little clearer and I thank you for your patience.

    Kind Regards

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,763
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    try and thoroughly test the following macro
    Code:
    Sub blah()
    Dim Result(1 To 7)
    For Each cll In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        Erase Result
        xx = Split(cll.Value)
        For j = 1 To Len(xx(0))
            If Not IsNumeric(Left(xx(0), j)) Then
                Result(1) = Left(xx(0), j - 1)
                Result(2) = Mid(xx(0), j)
                Exit For
            End If
        Next j
        If Left(xx(1), 2) Like "C#" Then
            If Mid(xx(1), 2, 1) < 8 Then
                Result(3) = Left(xx(1), 2)
                xx(1) = Mid(xx(1), 3)
            End If
        End If
        If Left(xx(1), 2) Like "#y" Then
            Result(4) = Left(xx(1), 2)
            xx(1) = Mid(xx(1), 3)
        End If
        If Right(xx(1), 2) Like "G#" Then
            If Right(xx(1), 1) < 4 Then
                Result(6) = Right(xx(1), 2)
                xx(1) = Left(xx(1), Len(xx(1)) - 2)
            End If
        End If
        If Len(xx(1)) > 0 Then Result(5) = xx(1)
        Result(7) = Split(xx(2), "K")(0)
        cll.Offset(, 1).Resize(, 7) = Result
    Next cll
    End Sub
    My trial sheet is set up thus:
     ABCDEFGH
    1DATA SOURCE       
    27GF C5Hc 4K7GFC5 Hc 4
    37Gd C43yHc 6K7GdC43yHc 6
    49St C5Md 21K9StC5 Md 21
    57St/Slw C43yHc 4K7St/SlwC43yHc 4
    612St/Fs C5Md 2K12St/FsC5 Md 2
    716Y/Sft HcH 815K16Y/Sft  HcH 815
    817Sft Ch 4K17Sft  Ch 4
    924Gd HcCh 5K24Gd  HcCh 5
    1022Hy NvChG3 23K22Hy  NvChG323
    117GS C12yG3 22K7GSC12y G322


    Excel tables to the web >> Excel Jeanie HTML 4
    with the results appearing in columns B:H. The results match you required results bar the value in cell H2 which I think you got wrong.

  8. #8
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Hi p45cal

    Unfortunately I am pretty useless with code and am unsure how to proceed with your solution. My apologies.

    Kind Regards

  9. #9
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,763
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  10. #10
    Board Regular
    Join Date
    Aug 2010
    Posts
    373
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data Between Spaces

    Hi p45cal

    Many thanks for your response and coding which I am sure will work a treat but unfortunatley I just don't have the knack for code, if and when I have time I may peruse the above links but it is just not possible at the moment so regrettably I will have to leave it there. Thanks for your patience.

    Kind Regards

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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