Need help extracting data from text

[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 - see attached as an example.

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

[AlKey]

Copy and paste this formula in E1 and drag to the right

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D1,"{",""),"}",""),"/",REPT(" ",LEN($D1))),(COLUMNS($B:B)-1)*LEN($D1)+1,LEN($D1)))

[jaslake]

Hi Clint0

Try this

Please Login or Register  to view this content.

[ClintO]

In response to AlKey: That works beautifully - thanks.

One follow up question. There is a little green flag in the upper left corner of every cell - warning me that the formula refers to empty cells. Is there a way to get rid of all of those little green flags all at once? Or do I need to click on each cell individually and then click on the instruction to ignore the error?

(BTW, I am using Excel for Macintosh 2011, version 14.3.9)

John: I will reply separately to you post - thanks to you too.

Clint

[AlKey]

To get rid of those just select all cells and then click on the little box and select Ignore.



You're welcome and thank you for your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[ClintO]

Hi John -
Thanks for the reply. Should I copy and paste that code into cell D1 on my worksheet? (Will this code work in Excel for Macintosh 2011?)

Cheers,
Clint

[jaslake]

Hi Clint

Will it run on a Mac...don't know...press Ctrl + x and see if it works.

The Code goes in a General Module. Go into VBA (Alt + F11).

[AB33]

I am not sure if the code works on Mac, but try to press the button on Column K.

[ClintO]

That did the trick - thanks! I am looking for where to mark the thread as solved. (John - thanks for your input also. I'll make the plunge into VBA at a some future date.)

[AlKey]

Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.