# Formula to show multiple text from cell

#### par4724

##### New Member
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: -
 G505 X 0.0 X_2 -0.020 Y -0.035 Z 0.005

<tbody>
</tbody>

Thank you,
Paul.

Last edited:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### kweaver

##### Well-known Member
I put your text string in A20.
First: in A22:

Code:
``=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.

Last edited:

#### kweaver

##### Well-known Member
Without the 2 helper columns, B23 (fill down) would look like this:

Code:
``=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

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