Getting a number from a string

[Ruffinshot]

Is there a simple macro (or spreadsheet) command that will pull a number from a string of text and numbers?

So if I have the string "buy 100 eggs" it would return "100".

Currently I'm chopping the string up into little bits with instr and mid and thinking, "There has to be a better way."

Any help would be greatly appreciated.

 

[Aladin Akyurek]

On 2002-08-28 11:12, Ruffinshot wrote:
I've been doing something similar. There are a couple of problems. I don't have enough room on the spreadsheets for one. They only go to the "IV" column and I'm doing 24 of these across. So that came out to a series of complicated if/iserror/search/then bits (so I could find out not only the number but what item). That's largely solved, but extremely slow, as in 4-5 seconds to recalculate every time I change a cell.

In the macro the string I'm cutting up (temp$, what else) can vary and have several items on a line. So it'd read something like "Buy 100 eggs 40000 pickles 2 Winnebagos 1745231 rolls" and so on. I just want to pick out say, the eggs and the rolls. So I'm using instr to find the start of the word, instr to find the double space (" ") after the word, and keeping the middle. When the item I'm looking for is the last one in the list I can't use the " " to find the end of it, so that's another conditional. It'd doable, but a pain in the neck. I thought I remembered something about a single command that would just return the numbers without all the headache. Then I could cut it up with instr and read the numbers without the second set of conditionals.

I don't think you'll be interested... If you have the morefunc add-in installed, you can use:

=WMID($A$1,MATCH(B1,EVAL(CHAR(123)&CHAR(34)&SUBSTITUTE($A$1," ",CHAR(34)&","&CHAR(34))&CHAR(34)&CHAR(125)),0)-1,1)+0

See the figure...

Book8
	A	B	C	D
1	Buy100eggs40000pickles2Winnebagos1745231rolls	eggs	100
2		rolls	1745231
Sheet1

 

[stevebausch]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q188007

is the page that discusses simulating VB6 string functions in VB5.

It can be done in VBA5, also. Just be advised that the enumerated types in some of the argument declarations will need to be replaced with Integers.

And, of course, the previously mentioned funky behaviors, which can be modified.

I have cleaned up the code; Replace() and Split() are being used in my current project.

(I just don't feel like sharing the patches)

 

[Dave Patton]

Gentlemen:

With Excel 97 and

=MSUBSTITUTE(UPPER(E5),CHAR(ROW($1:$26)+64),"")+0

I get a value error.

I used MSubstitute successfully previously.

With MoreFunc, I used

=WMID(A7,2,1," ")

Does anyone know why I would not get the MSubstitute to work?

 

[Juan Pablo González]

That formula should be Array Entered. If not, you should get #VALUE

 

[Dave Patton]

On 2002-08-28 19:57, Juan Pablo G. wrote:
That formula should be Array Entered. If not, you should get #VALUE

I get the Value error even if I Array Enter the formula.

Dave

 

[Juan Pablo González]

Try stepping through the code of MSUBSTITUTE, I haven't really tested well with 97, but it doesn't use anything "unusual"...

 

[Ruffinshot]

A couple of questions - 1) How do I install the morefunc add-in? I don't see it on the list of add-ins. 2) How do I go about putting in a user defined function (i.e. where does it go)?

 

[Aladin Akyurek]

On 2002-08-29 05:49, Ruffinshot wrote:
A couple of questions - 1) How do I install the morefunc add-in? I don't see it on the list of add-ins. 2) How do I go about putting in a user defined function (i.e. where does it go)?

Question 1

( 1.) Download morefunc.zip from

http://longre.free.fr/english/index.html

( 2.) Unzip it and read the readme file which tells you where to put certain files.

( 3.) After step 2, it's only a matter of activating the morefunc add-in via Tools|Add-Ins.

Question 2

( 1.) Activate the target workbook.

( 2.) Copy the code for the UDF.

( 3.) Activate Tools|Macro|Visual Basic Editor.

( 4.) Activate Insert|Module.

( 5.) Paste the copied code in the window entitled "...(Code)".

( 6.) Activate File|Close and Return to Microsoft Excel.

 

[Ruffinshot]

Got morefunc, thanks. New toys....

For the UDFs, what if I want to write my own? Or maybe someone knows something that can do what I'm looking for without me straining anything?

Currently I have one sheet with about 20,000 indirect/match functions that are slowing everything down. Most look something like:

=IF(ISERROR(INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B:$B,0),2)))),"",INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B:$B,0),6))))

Cleaning up the match so it's only checking the 40 or so cells I actually want to check will help, but is there a simpler way to look return a match?

 

[Aladin Akyurek]

On 2002-08-29 07:15, Ruffinshot wrote:
Got morefunc, thanks. New toys....

For the UDFs, what if I want to write my own? Or maybe someone knows something that can do what I'm looking for without me straining anything?

Currently I have one sheet with about 20,000 indirect/match functions that are slowing everything down. Most look something like:

=IF(ISERROR(INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B:$B,0),2)))),"",INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B:$B,0),6))))

Cleaning up the match so it's only checking the 40 or so cells I actually want to check will help, but is there a simpler way to look return a match?

Is the data area of interest in Reference in columns B to F?

Does this area change frequently, that is, crimp and expand?

Is B of numeric type?