How to extract number from string?

Hi 4th Space,

This method uses FIND to look for positions of all 5 spaces, then MID to pull out the price. This will work for any length words before the price.

Formula in B2

=FIND(" ",$A2,1)

(The 1 is not required, but I put it in for consistency)

Formula in C2

=FIND(" ",$A2,B2+1)

Formula in D2

=FIND(" ",$A2,C2+1)

Formula in E2

=FIND(" ",$A2,D2+1)

Formula in F2

=FIND(" ",$A2,E2+1)

Formula in G2

=MID(A2,E2+1,F2−E2)

Regards,

Ian.

Hi 4thSpace,

I think Ian's solution meets your citeria. I am wondering if it is possible to make a close guess as to where that 4th space is likely and eliminate some of the columns. All my formulas are based on Ian's.

Here are some assumptions. Are they true?

a. 2014- always 4 characters

b. NOV- always 3 chars

c. 22- always 2 chars

d. HLF- 3-4 characters

If a-c are true and d is either 3 or 4 chars then

=FIND(" ",$A1,15)

will find that 4th space and we don't need columns b-d

If the date is sometimes 1 char and HLF can be 2 chars then we cannot predict where the 4th space will be reliably without additional columns.

=FIND(" ",$A1,18) will find the 5th space if the price is at least 4 chars (1.00) even if d and c are too short for the above.

In Row 1 we have the reduced columns- the formulas should have "=" in front of them.

In Row 2 I have bundled it all together for you.

Here is a copy and paste

B2 =MID($A2,FIND(" ",$A2,15),FIND(" ",$A2,18)−FIND(" ",$A2,15))

quinn

ps these solutions both provide a text result. If you want to do calculations with these you will need to use VALUE().

AppleScript can provide a quick and clean solution (no extra columns). Copy the script below and paste into AppleScript Editor. Then just select the cells containing the option prices, then hit the green triangle run button, click once in a cell where you want the numbers (strike price?) to start, and command-v to paste.

SG

tell application "Numbers" to tell front document's active sheet

set t to first table whose selection range's class is range

set s to ""

repeat with c in t's selection range's cells

set v to c's value

set s to s & v's fifth word & return

end repeat

set the clipboard tos

end tell

If the price always has a decimal point, you can use wildcards in the SEARCH function.

=MID(A1,SEARCH("??.??",A1),5)

Hi T2YUKI,

Like!

Our next challenge is to find the price if it is not always 5 characters long. Easy to find the position of the decimal point with

=SEARCH(".",A2)

But if the price is not 5 characters long, or if the preceding text should vary, we must work backwards from the position of the decimal point to find the space before the price.

An interesting problem!

Regards,

Ian.

Hi Ian,

You’re right. If the character length of the price change, that formula doesn’t work properly.

I tried a formula: =SEARCH("??.?0 PUT",A2), but it doesn’t extract the prince "300.00" correctly.

copy the data into a text editor and replace all the spaces with tabs then copy and paste back into Numbers. each item (field) will now be in its' own column

Hi T2YUKI,

I like SGIII's AppleScript solution (find the fifth word). Neat solution, SG! 🙂.

In Numbers, (without AppleScript) it is an interesting challenge.

This forum does not always show screen shots. I have given up on screen shots, and this table was copied and pasted from Numbers. To make it easier when referring to cells, I have added another column (new column A) and another row (row 1).

Formula in C3 (and Fill Down

=SEARCH(".",B3)

(Assuming that the decimal point is the first "dot" in A3 B3)

Formula in D3 (and Fill Down)

=MID(B3,SEARCH("??.??",B3),5)

But that misses the first character (3) in 300.00 and adds the space before 9.00 and 8.50

Formula in E3 (and Fill Down)

=SEARCH(" ",B3,C3)

That is, look for the next space in B3, starting from the decimal point.

Previous space is our next challenge.

... I just saw an email reply from Wayne...

copy the data into a text editor and replace all the spaces with tabs then copy and paste back into Numbers. each item (field) will now be in its' own column

How simple! Thanks, Wayne! 🙂.

Regards,

Ian.

Edit (Assuming that the decimal point is the first "dot" in A3 B3)

Hi,

To get the text between the 4th space and the 5th space, you can use this formula:

=SUBSTITUTE(RIGHT(B2,(LEN(B2)−(SEARCH("|",SUBSTITUTE(B2," ","|",4),1)))),RIGHT(B2,(LEN(B2)−(SEARCH("|",SUBSTITUTE(B2," ","|",5),1) − 1))),"",1)

We must ensure that these three characters | does not exist in the string, otherwise change these characters in the formula.

Hi Ian,

(find the fifth word)

Glad to see you spotted that. AppleScript really *is* simple (sometimes).🙂

Best,

SG

Quote: "AppleScript really *is* simple (sometimes)."

Perhaps I should have raised this in the OS X Forum, but for the moment I am focussed on using Numbers.

In the last few days I have been considering learning about Applescript.

I suppose what I'm thinking of is how far does it make things easier perhaps to "open up" Numbers perhaps more than the available functions - although they are quite comprehensive.

Any guidance would be useful. I presume it is generally applicable across MAC OS and utilities.

Regards

Hi 4thSpace

How do you run Apple Script in Numbers?

Just follow the directions in my post above. Running an existing script is really easy (easier than posting something successfully on this forum). Writing a script has a learning curve.

SG

This page (and theother pages on the site) may help get you started.

SG