Formula to show multiple text from cell

par4724

New Member
Joined
Mar 26, 2008
Messages
14
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:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,074
Office Version
365, 2010
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">X</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">72</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">77</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">X_2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-0.020</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">83</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">93</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Y</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">-0.035</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">99</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">107</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Z</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.05</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">113</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">119</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Z_2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.005</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">125</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">134</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B23</th><td style="text-align:left">=MID(<font color="Blue">$A$20,C23+LEN(<font color="Red">A23</font>)+1,D23-C23-(<font color="Red">LEN(<font color="Green">A23</font>)+1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C23</th><td style="text-align:left">=FIND(<font color="Blue">$A23,$A$20,FIND(<font color="Red">":",$A$20</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D23</th><td style="text-align:left">=FIND(<font color="Blue">"+",$A$20,C23</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,074
Office Version
365, 2010
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
Joined
Mar 26, 2008
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,350
Messages
5,486,349
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top