Extract Data Between Spaces

[Celticshadow]

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

[TABLE="width: 666"]
<tbody>[TR]
[TD]DATA SOURCE[/TD]
[TD="colspan: 7, align: center"]REQUIRED DATA
[/TD]
[/TR]
[TR]
[TD]7GF C5Hc 4K[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]GF[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Hc[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]7Gd C43yHc 6K[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]GD[/TD]
[TD="align: center"]C4[/TD]
[TD="align: center"]3y[/TD]
[TD="align: center"]Hc[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD]9St C5Md 21K[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]ST[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Md[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD]7St/Slw C43yHc 4K[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]St/Slw[/TD]
[TD="align: center"]C4[/TD]
[TD="align: center"]3y[/TD]
[TD="align: center"]Hc[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]12St/Fs C5Md 2K[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]St/Fs[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Md[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]16Y/Sft HcH 815K[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]Y/Sft[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]HcH[/TD]
[TD="align: center"][/TD]
[TD="align: center"]815[/TD]
[/TR]
[TR]
[TD]17Sft Ch 4K[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]Sft[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ch[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]24Gd HcCh 5K[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]Gd[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]HcCh[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]22Hy NvChG3 23K[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Hy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NvCh[/TD]
[TD="align: center"]G3[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD]7GS C12yG3 22K[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]GS[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]2Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]G3[/TD]
[TD="align: center"]22
[/TD]
[/TR]
</tbody>[/TABLE]

 

[p45cal]

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.

 

[Celticshadow]

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

 

[Celticshadow]

Hi All

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

Kind Regards

 

[p45cal]

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?

 

[Celticshadow]

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

 

[p45cal]

try and thoroughly test the following macro

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:

Excel Workbook
	A	B	C	D	E	F	G	H
1	DATA SOURCE
2	7GF C5Hc 4K	7	GF	C5		Hc		4
3	7Gd C43yHc 6K	7	Gd	C4	3y	Hc		6
4	9St C5Md 21K	9	St	C5		Md		21
5	7St/Slw C43yHc 4K	7	St/Slw	C4	3y	Hc		4
6	12St/Fs C5Md 2K	12	St/Fs	C5		Md		2
7	16Y/Sft HcH 815K	16	Y/Sft			HcH		815
8	17Sft Ch 4K	17	Sft			Ch		4
9	24Gd HcCh 5K	24	Gd			HcCh		5
10	22Hy NvChG3 23K	22	Hy			NvCh	G3	23
11	7GS C12yG3 22K	7	GS	C1	2y		G3	22
Sheet

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.

 

[Celticshadow]

Hi p45cal

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

Kind Regards

 

[p45cal]

Take a look at some of the following sites:
Getting Started with Macros and User Defined Functions
Introduction to VBA & Excel Macros - What are they & Writing your First Macro using Excel | Chandoo.org - Learn Microsoft Excel Online
Excel VBA Tutorial 1 Creating your first Macro - Hello World! - YouTube
Learning about EXCEL macros

 

[Celticshadow]

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