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]

 

[Marcol]

Formula for this type of problem is usually long winded

Option 1 uses Helpers
If you are feeling particularly masochistic you could do without the helpers, Option 2

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

Excel Workbook
	A	B	C	D	E	F	G	H
1	DATA SOURCE	REQUIRED DATA
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
Sheet1 (2)

 

[Celticshadow]

Hi Marcol

Many many thanks your above formula works an absolute treat and I am extremely grateful for your input. I can only echo again my previous praise for other posts that have been solved by forum members, this forum is worth its weight in gold with such members as Marcol and p45cal and their personal font of knowledge. Marcol take a bow son and many thanks, I really appreciate your help.

p45cal has also been extremely helpful and if I was more savvy with code I have no doubt that his solution would have worked also and I am also very grateful for the patience and knowledge shown by said member. Thanks again p45cal.

This forum never fails to amaze me and I encourage all members whom have been helped to leave positive feedback and be grateful such generous persons are available to lend their time and knowledge.

Kind Regards