Need help extracting numbers from a text string

[Clinto]

I have been humbled (once again) by Excel! I am running tests on prototypes of a new fitness device. As part of our tests, we collect data from an accelerometer. I receive the data in this format: {X,Y,Z}. I can transfer the data from our test fixture to an Excel file. (I have an example in an Excel file, but I couldn't see how to attach it to this post.)


The accelerometer data are in column D. I want to extract the X, Y and Z values and put them in columns E, F and G. I tried using the MID function to extract the data, but that didn't work because the length of the data varies. For example, it could be a single-digit number (such as 5). Or it could be a two-digit number (such as 17 or -8). It could be a three-digit number (such as -21). In rare cases, it could be a three-digit positive number (such as 126) or a four-digit negative number (such as -208).


With thanks in advance, does anybody have any suggestions for extracting the values of X, Y and Z and placing them in separate columns?


Cheers,
Clint

 

[István Hirsch]

Re: SOLVED: Need help extracting numbers from a text string

Try this with less functions. Enter into E1 then drag it down and right:

=1*SUBSTITUTE(MID(SUBSTITUTE($D1,"/",REPT(" ",99)),2+(COLUMN(A1)-1)*99,99),"}","")