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

1. ## 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. ## 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.
It's likely the solution will involve a macro.

3. ## Re: Extract Data Between Spaces

Originally Posted by 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.
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. ## 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. ## 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?

6. ## 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. ## 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:
 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

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. ## 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

10. ## 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

## User Tag List

#### Posting Permissions

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