ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Isolating text and numbers (https://www.excelbanter.com/excel-discussion-misc-queries/140521-isolating-text-numbers.html)

[email protected]
Isolating text and numbers
 
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

Stefi
Isolating text and numbers
 
For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi


€ť ezt Ă*rta:

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

Ron Coderre
Isolating text and numbers
 
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


" wrote:

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

[email protected]
Isolating text and numbers
 
Thanks for your help. Your formula doesnt work when X=0 or Y=0. Is
there a way to isolate that too? Thanks

Swamy

On Apr 26, 9:12 am, Stefi wrote:
For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi

" ezt írta:



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 -

[email protected]
Isolating text and numbers
 
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

On Apr 26, 10:26 am, Ron Coderre
wrote:
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



" wrote:
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 -

Stefi
Isolating text and numbers
 
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


€ť ezt Ă*rta:

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

Swamy

On Apr 26, 9:12 am, Stefi wrote:
For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi

" ezt Ă*rta:



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 -

[email protected]
Isolating text and numbers
 
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




On Apr 27, 2:54 am, Stefi wrote:
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

" ezt írta:



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

Swamy

On Apr 26, 9:12 am, Stefi wrote:
For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi

" ezt írta:

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 -- Hide quoted text -

- Show quoted text -

Pete_UK
Isolating text and numbers
 
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

On Apr 27, 1:27 pm, wrote:
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

On Apr 27, 2:54 am, Stefi wrote:



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

" ezt írta:

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

Swamy

On Apr 26, 9:12 am, Stefi wrote:
For X value: =VALUE(MID(A1,2,FIND("Y",A1)-2))
For Y value: =VALUE(MID(A1,FIND("Y",A1)+1,256))

Regards,
Stefi

" ezt írta:

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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Black Icarus
Isolating text and numbers
 
On 26 Apr 2007 05:54:11 -0700, 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))


All times are GMT +1. The time now is 10:07 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com