# Thread: Formula to show multiple text from cell Thanks: 0 Likes: 0

1. ## Formula to show multiple text from cell

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

Thank you,
Paul.

2. ## Re: Formula to show multiple text from cell

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.

ABCD
23X0.07277
24X_2-0.0208393
25Y-0.03599107
26Z0.05113119
27Z_20.005125134

Sheet3

Worksheet Formulas
CellFormula
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)

3. ## Re: Formula to show multiple text from cell

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))`

4. ## Re: Formula to show multiple text from cell

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.