How to extract number from string?

While in Numbers, in the bar across the top of your screen you should see a "scroll" symbol like this.

Open Scripts Folder > Open Numbers Scripts Folder should open a folder where you can save the script with your preferred name for easy access up there, just like an ordinary menu pick.

If you end up needing to run it very often, then you could consider placing the script in what's called an Automator Service (you open Automator and create a service). This will create an entry in the Numbers > Services menu and then you use System Preferences > Keyboard to assign a shortcut.

SG

Sure wish they would fix the problem with posting images.

Have a look at this page. It's a different app of course, but the principle is the same.

SG

The Script Editor will save scripts in iCloud or on your Mac. If you save them in iCloud they will be accessible by all your Macs (and you'll have the same version on each). But to run a script on a Mac, I believe the most convenient thing is to copy it into the relevant scripts folder. That way running it is just like choosing an item from a menu.

SG

Hi Jacques,

Your formula is great. I was astonished at finding that it works perfectly. But what is the mysterious string of "|"? it seems that the formula: =SEARCH("|",SUBSTITUTE(B2," ","|",4),1) returns the position of the 4th space in a string in B2 cell. Why and how does it work?

Hi T2YUKI,

T2YUKI wrote:

Hi Jacques,

Your formula is great. I was astonished at finding that it works perfectly. But what is the mysterious string of "|"? it seems that the formula: =SEARCH("|",SUBSTITUTE(B2," ","|",4),1) returns the position of the 4th space in a string in B2 cell. Why and how does it work?

It would be easier if there was an occurrence option with the SEARCH function like the SUBSTITUTE function

So, I use SUBSTITUTE to replace the 4th space by an weird string (to be sure that these three consecutive characters do not exist in cell's value) --> "|"

After, the search function find these characters "|" and return the position, RIGHT return all characters after these character |

I use the same function to get the characters after the 5th space.

The =SUBSTITUTE, use the string in the result of the second RIGHT function to remove it in the string of the result of the first RIGHT function

Thank you very much for your kindness, Jacques.

I've understood how the formula works now. The SUBSTITUTE function replaces the 4th occurrence of space characters with the "|" string and the SEARCH function returns the position of the replacement. The mysterious string has a reason for being mysterious so that the source string will never contain a new string to substitute.

Many thanks - I'll have a look.

Regards

Hi 4thSpace:

How would the date look in your example if it were November the third. If the day of the month is always

a two digit number this formula will work:

=VALUE(MID(A,17,LEN(A)-20))

with A being the column that the data is in.

Of if the day is one digit then:

=VALUE(MID(A,16,LEN(A)-19))

If you don't care if the result is a number or not, leaving off the VALUE() will return the number as a string.

Just another idea.

mswint