Extract Data Between Spaces

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
373
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:
[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]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

Active Member
Joined
Aug 16, 2010
Messages
373
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

Active Member
Joined
Aug 16, 2010
Messages
373
Hi All

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

Kind Regards
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

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:

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
373
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763

ADVERTISEMENT

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:
Excel Workbook
ABCDEFGH
1DATA SOURCE
27GF C5Hc 4K7GFC5Hc4
37Gd C43yHc 6K7GdC43yHc6
49St C5Md 21K9StC5Md21
57St/Slw C43yHc 4K7St/SlwC43yHc4
612St/Fs C5Md 2K12St/FsC5Md2
716Y/Sft HcH 815K16Y/SftHcH815
817Sft Ch 4K17SftCh4
924Gd HcCh 5K24GdHcCh5
1022Hy NvChG3 23K22HyNvChG323
117GS C12yG3 22K7GSC12yG322
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

Active Member
Joined
Aug 16, 2010
Messages
373
Hi p45cal

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

Kind Regards
 

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
373
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,468
Messages
5,636,494
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top