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

 

[bmorro]

Will you try and post just a few lines of data or manually enter some data to give us an idea of what you're looking for.

 

[Jonmo1]

You could do this in a couple steps

First using Excel's Find/Replace Tool (press CTRL+F)
You can replace { with Nothing, and repeat for }

Then use Data - Text to columns - Deliminated - Check Comma - Finish


Hope that helps.

 

[BiocideJ]

Text to Columns would certainly be a fast way if you do it infrequently, otherwise, the following formula may be more appopriate if you are putting the information in often.

in cell E2
=VALUE(LEFT(D2,FIND(",",D2)-1))

in cell F2
=MID(D2,FIND(",",D2)+1,FIND(",",D2,FIND(",",D2))-1)

in cell G2
=MID(D2,FIND(",",D2,FIND(",",D2,FIND(",",D2)+1))+1,LEN(D2))

drag the formulas down for all necessary rows.

EDIT: I Just re-read your post and noticed you said the format was {X, Y, Z}. Do you literally mean that the output is {1,2,3} or were the swirly braces just to show that it was a list? If the output includes the braces, my formulas will need to be amended.

 

[Clinto]

SORRY! The are not commas between the numbers. There are slashes - this character: /

Yes - the swirly braces are included in the data that I have. Here is an example of a few rows in column D:
{5/7/10}
{-3/28/15}
{44,/9/-124}
{208/8/-39}

 

[BiocideJ]

OK. I actually found a problem with my middle number formula anyway so here is the corrected version given your new criteria of slashes also.

cell E2
=VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(D2,FIND("/",D2)-1),"{",""),"}",""))

cell F2
=VALUE(SUBSTITUTE(SUBSTITUTE(MID(D2,FIND("/",D2)+1,FIND("/",D2,FIND("/",D2)+1)-FIND("/",D2)-1),"{",""),"}",""))

cell G2
=VALUE(SUBSTITUTE(SUBSTITUTE(MID(D2,FIND("/",D2,FIND("/",D2,FIND("/",D2)+1))+1,LEN(D2)),"{",""),"}",""))

 

[Clinto]

SOLVED: Need help extracting numbers from a text string

Thanks again! Here is a reply I got from another forum that also worked well:

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)))

 

[BiocideJ]

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

That works, although it is generally discouraged to cross-post without notifying. The only thing I would note for that formula is that it leaves the resulting output as TEXT. To fix this, the entire formula should be surrounded with a VALUE(...) formula.

 

[Clinto]

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

That works, although it is generally discouraged to cross-post without notifying. The only thing I would note for that formula is that it leaves the resulting output as TEXT. To fix this, the entire formula should be surrounded with a VALUE(...) formula.

Sorry if I violated a rule. I was just trying to get an answer as quickly as possible.

And extra thanks for noting that the other solution saved the values as text, not numbers. That explains the frustration I've been having for the past half-hour trying to create a chart of my data.

 

[Rick Rothstein]

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

Sorry if I violated a rule. I was just trying to get an answer as quickly as possible.

We know why you were doing it, here is why you should have told us (and also told the other forum) that you cross posted your question. As a volunteer, I see your question on one of the forums you posted on, so I decide to help you. I spend a considerable amount of time digesting your question, formulating an approach to solving it, then crafting the formula or VBA code to implement the solution and then, finally, posting it to your thread... only to later find that someone else in the other forum (which I did not know about at the time I posted my solution) already gave you the same solution I came up with an hour earlier. Net result... you wasted my time and, more importantly, you withheld my being able to help someone else (who, just like you, needed help) for the time period I spent giving you an answer you no longer needed. Not a very fair thing for you to have done if you think about, right? On top of that, you also suffer a loss from cross posting without tell each forum you did so... what if the solution you get from the forum I am not on is flawed in some way that neither you or the volunteer who created it noticed... if I saw it, I may notice the flaw and comment on it, but because you did not tell me you asked the same question in another forum, I did not know of the other (flawed) solution you received in order to be able to have commented on it. And trust me, once I find out you wasted my time by allowing me to develop a solution that might have (even if it hadn't, but just might have) already been given to you, my inclination would be to just abandon the question without checking your other solutions for problems at all. So, the moral of this message... when you ask your question in multiple forums, add a note in your message IN BOTH FORUMS advising you posted the same question elsewhere AND PROVIDE A LINK TO THAT OTHER FORUM'S THREAD FOR YOUR QUESTION so that the volunteers can see what responses you have already gotten before proceeding.