Extracting numbers (Coordinates) from string

[kratk]

Hi:

How can i leave only the coordinates and supress the text from the string next string:

Command: ID Specify point: X = 5068.7648 Y = 224.3896 Z = -4063.3161

When i copy this from the AutoCad, it copies it the whole text only in the first cell (A1 for example)
This is for a large list of points (exactly as the showed above)..... So i need, basicly, to convert the data pasted in one cell to the next format:

Xvalue Yvalue Zvalue.

At the moment i'm using the TextToColumn tool and manually deleting the columns that i don't need but is extremely slow..... so thats i'm asking for your valuable help.

Thank A lot

 

[tushiroda]

try this. you can read through the formulas and tweak it as necessary

Excel Workbook
	A	B	C	D
1	Command: ID Specify point: X = 5068.7648 Y = 224.3896 Z = -4063.3161	5068.7648	224.3896	-4063.3161
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=MID($A1,FIND("X =",$A1)+3,FIND("Y =",$A1)- FIND("X =",$A1)-4)
C1	=MID($A1,FIND("Y =",$A1)+3,FIND("Z =",$A1)- FIND("Y =",$A1)-4)
D1	=TRIM(RIGHT($A1,LEN($A1)-FIND("Z =",$A1)-3))

 

[kratk]

Tushiroda:

It's not working, i entered the formulas that you wrote in the cells b1, c1 & d1, respectively, and nothing. My excel is the 2003 version, it's in english.... Maybe there's the problem. I hope that you can make it work because your way it's a perfect way to go.

 

[mole999]

which bit doesn't work

I've just tried it in 2003 and it works as advertised

 

[j_latendre]

Try this:

=MID(B17,FIND(" ",B17,3),15)

Change B17 to appropriate cell.

Jesse

 

[tushiroda]

when you say it is not working, what do you get in the cells with formulas. Also, is what I have in cellA1 what you have in cell A1 (meaning is it text vs. an image/html of some kind possibly)? I'm on 2007 US though that shouldn't matter here.

You can copy in the text from the post to test with as well.

 

[Haseeb Avarakkan]

If your numbers are always after the '=' sign, try;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Command: ID Specify point: X = 5068.7648 Y = 224.3896 Z = -4063.3161</td><td style="text-align: right;;">5068.765</td><td style="text-align: right;;">224.3896</td><td style="text-align: right;;">-4063.32</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LOOKUP(<font color="Blue">100^100,--LEFT(<font color="Red">TRIM(<font color="Green">MID(<font color="Purple">$A1,FIND(<font color="Teal">"^",SUBSTITUTE(<font color="#FF00FF">$A1,"=","^",COLUMNS(<font color="Navy">$B1:B1</font>)</font>)</font>)+1,250</font>)</font>),ROW(<font color="Green">$A$1:$A$20</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy across & down...

 

[kratk]

The formula doesn't do anything, maybe is a format issue but i can't make it work. The folowing message is shown when the formulas are entered:
"The formula you typed contains an error"

In the cell A1 is a string, not an image, and is the same as you have in the example. I need exactly what you have posted....... WTF!!!!

Here is the image of the result, but i don't have it iploaded in an image server.....?????

So.... Bottom line, in my sheet when i copy your codes, they copy also as a string, i mean, they doesn't work as a formula, and when i try to edit it, an error shows "The formula........"

 

[mole999]

OK, what system / keyboard language are you using, sometime commas and semicolons have different results dependant on regional settings
they do work on english layout

 

[kratk]

Nevermind, like i told you before, it was a format error...... i look carefully to the sintax help of the command and my version of excel works with this kind of comma ";" not with the ","..... so i change every comma for the ";" and works perfectly.

Thanks a lot for your patience and your availability.