Formula to show multiple text from cell

[par4724]

Hi all,

I have the below text which I have imported from a text file into Sheet 2, Cell A28. I would like to extract parts of the text into Sheet 1 as below. The X, X_2, Y, Z, Z_2 are axis of a machine and will never change. The numbers after will change. From other posts I think I might need the IFERROR and FIND formula but I'm no expert. Any help would be greatly appreciated.


N80 $P_UIFR[5]=CTRANS(X,63,X_2,63,Y,106.05,Z,-70,Z_2,-70,A,0.0) :CFINE(X,0.0+R320,X_2,-0.020+R324,Y,-0.035+R321,Z,0.05+R322,Z_2,0.005+R323,A,0.0) ;G505 A0 = 20001, 21221, 21224, 21201, 21204, 25601-03 COM.CH.

From Sheet 2 to be displayed in Sheet 1, A2, A3, A4, A5 as follows: -
[TABLE="width: 96"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]G505
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X
[/TD]
[TD="bgcolor: transparent, align: right"]0.0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X_2
[/TD]
[TD="bgcolor: transparent, align: right"]-0.020

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]-0.035
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Z
[/TD]
[TD="bgcolor: transparent, align: right"]0.005
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
Paul.

 

[kweaver]

I put your text string in A20.
First: in A22:

=MID(A20,1+FIND(";",A20),4)

Returns the G505 under the assumption all of the text structure will be the same and the code (G505) will always be 4 characters.
If not, another calculation would have to be done.

Then, I used 2 helper columns.

Book1
	A	B	C	D
23	X	0.0	72	77
24	X_2	-0.020	83	93
25	Y	-0.035	99	107
26	Z	0.05	113	119
27	Z_2	0.005	125	134
Sheet3

Cell Formulas
Range	Formula
B23	=MID($A$20,C23+LEN(A23)+1,D23-C23-(LEN(A23)+1))
C23	=FIND($A23,$A$20,FIND(":",$A$20))
D23	=FIND("+",$A$20,C23)

 

[kweaver]

Without the 2 helper columns, B23 (fill down) would look like this:

=MID($A$20,FIND($A23,$A$20,FIND(":",$A$20))+LEN($A23)+1,FIND("+",$A$20,C23)-FIND($A23,$A$20,FIND(":",$A$20))-(LEN($A23)+1))

 

[par4724]

Thank you so much for spending the time resolving this. I usually find that I have not made myself clear and have to come back with another question but this time it's done in one.
Very grateful,
Paul.