Isolating text and numbers

[mahadevan.swamy]

Hi,

I have a text file and in this text file, the data is like: -
X3.8687 Y1.6025
X1.6025 Y3.8687
X0 Y4.1875
X-1.6025Y3.8687
X-3.8687Y1.6025
X-4.1875Y0
X-3.8687Y-1.6025
X-1.6025Y-3.8687
X0 Y-4.1875
X1.6025 Y-3.8687
X3.8687 Y-1.6025

I have created two columns in the spreadsheet : X and Y. What I want
to do is to come up with a function that will read the X and Y values
from this input data. I will copy and paste this current data in one
column and in the other 2 columns, I must see X and Y values (in
numbers). I have tried using MID functions, but I am having problems
reading Y values. Any ideas?

Thanks

 

[Guest]

For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi


„[email protected]†ezt írta:

 

[Guest]

With your posted data in Col_A beginning in A2

Since it appears that the data is effectively "fixed width", try this:

Select the Col_A data

From the Excel main menu:
<data><text to columns>
Check: Fixed Width.............click [Next]
Insert break points: After the "x", Before the "y", After the "y"
Click [Next]
Set the 1st and 3rd columns to be SKIPPED
Set the destination cell to: B2
Click [Finish]

That should parse the x- values into Col_B and the y-values into Col_C.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[mahadevan.swamy]

Thanks for your help. Your formula doesnt work when X=0 or Y=0. Is
there a way to isolate that too? Thanks

Swamy

 

[mahadevan.swamy]

Hi Ron,

That is a good idea. But I have to tell you that I'll be repeatedly
copying data from text files and pasting it on to A column. After I
get values for X and Y columns, I will copy that data and paste it
onto another workbook. I tried your method and it works for only the
data I posted. But if i copy and paste a fresh set of data onto column
A, the values dont change. I want to do this in a quick and efficient
manner as I have lots of text files.

Also I want X and Y columns to be together so I can easily copy that
off and paste it in another workbook.
Thanks for your help

Swamy

With your posted data in Col_A beginning in A2

Since it appears that the data is effectively "fixed width", try this:

Select the Col_A data

From the Excel main menu:
<data><text to columns>
Check: Fixed Width.............click [Next]
Insert break points: After the "x", Before the "y", After the "y"
Click [Next]
Set the 1st and 3rd columns to be SKIPPED
Set the destination cell to: B2
Click [Finish]

That should parse the x- values into Col_B and the y-values into Col_C.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hi,

I have a text file and in this text file, the data is like: -
X3.8687 Y1.6025
X1.6025 Y3.8687
X0 Y4.1875
X-1.6025Y3.8687
X-3.8687Y1.6025
X-4.1875Y0
X-3.8687Y-1.6025
X-1.6025Y-3.8687
X0 Y-4.1875
X1.6025 Y-3.8687
X3.8687 Y-1.6025

I have created two columns in the spreadsheet : X and Y. What I want
to do is to come up with a function that will read the X and Y values
from this input data. I will copy and paste this current data in one
column and in the other 2 columns, I must see X and Y values (in
numbers). I have tried using MID functions, but I am having problems
reading Y values. Any ideas?

Thanks- Hide quoted text -

- Show quoted text -

 

[Guest]

Your example contained both X=0 and Y=0 and I tested the formulae also for
these cases and they worked. Please post the exact data for which the
formulae don't work!

Stefi


„[email protected]†ezt írta:

 

[mahadevan.swamy]

Your formula worked when Y=0 but not when X=0. I get the #VALUE error.
Did you get that kind of error? I am posting the data from the X
column

X Y
X3.8687 Y1.6025 3.8687 1.6025
X1.6025 Y3.8687 1.6025 3.8687
X0 Y4.1875 #VALUE! 4.1875
X-1.6025Y3.8687 -1.6025 3.8687
X-3.8687Y1.6025 -3.8687 1.6025
X-4.1875Y0 -4.1875 0
X-3.8687Y-1.6025 -3.8687 -1.6025
X-1.6025Y-3.8687 -1.6025 -3.8687
X0 Y-4.1875 #VALUE! -4.1875
X1.6025 Y-3.8687 1.6025 -3.8687
X3.8687 Y-1.6025 3.8687 -1.6025

 

[Pete_UK]

Hi Swarmy,

you need to examine the cells which start with X0 more carefully -
perhaps you have non-printable characters which look like spaces after
the X0 which are causing problems to the VALUE function.

Hope this helps.

Pete

 

[Black Icarus]

On 26 Apr 2007 05:54:11 -0700, (e-mail address removed) wrote:

Simplest example assuming the initial data is in column A,

X value extraction formula is =MID(A6,FIND("X",A6)+1,7)
Y value extraction formula is =RIGHT(A6,LEN(A6)-FIND("Y",A6))