Formula or Macro to extract Data

[howard]

I have the follow data in Col A

I need a formula or macro to do the following

1) The years after notes must be extracted/copied to Col C & D in the same row
2) R R -each of these to be extracted/copied to Col C & D


I have manually extracted the data o sow what it should look like

Book2
	A	B	C	D
1	Notes 2020 2019		2020	2019
2	R R		R	R
Sheet1

It would be appreciated if someone could kindly assist me

 

[Radoslaw Poprawski]

provide bigger sample.
this should be easy to do in power query.

 

[howard]

Thanks for the reply

See larger sample below-hope this helps

Book5
	A	B	C	D
1	BR1 LTd STATEMENT OF COMPREHENSIVE INCOME
2	for the year ended 28 February 2020
3	Notes 2020 2019
4	R R
5	Revenue	12	144864.66	213244.18
6	Cost of sales		-115719.54	-177804.63
7	Gross profit		29145.11	35439.54
Sheet1

 

[Radoslaw Poprawski]

Book2
	A	B	C	D
1	BR1 LTd STATEMENT OF COMPREHENSIVE INCOME
2	for the year ended 28 February 2020
3	Notes 2020 2019		2020	2019
4	R R		R	R
5	Revenue	12	144864.66	213244.18
6	Cost of sales		-115719.54	-177804.63
7	Gross profit		29145.11	35439.54
Sheet1

Cell Formulas
Range	Formula
C3	C3	=--MID(A3,FIND(" ",A3,1)+1,(FIND(" ",A3,FIND(" ",A3,1)+1))-(FIND(" ",A3,1)+1))
D3	D3	=--TRIM(MID(A3,FIND(" ",A3,FIND(" ",A3,1)+1)+1,LEN(A3)))
C4	C4	=TRIM(MID(A4,1,FIND(" ",A4,1)-1))
D4	D4	=TRIM(MID(A4,FIND(" ",A4,1)+1,LEN(A4)))

 

[howard]

Many thanks Radoslow

Your formula's works perfectly

 

[Radoslaw Poprawski]

ur welcome
mark it as solution pls