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.

 

[Ruffinshot]

Modified the search area to only the area I need (can't believe I missed it before)... cut my calculate time down to about 2 seconds.

The corrected formula is:
=IF(ISERROR(INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B$1:$B$44,0),2)))),"",INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B$1:$B$44,0),6))))

I'm matching a two letter code (VA,NC,MD, etc.) from Reference! column B, rows 1 through 44, to get the row, then using indirect to get figures from the columns to the right. The search area never changes. The information being pulled out is between columns B and J.

The other match I'm using is matching text with column C to get the 2 letter code to match with column B.

The only other way I can see (though I'm probably missing something) to speed it up would be to take the IF/ISERROR out so it only has to match once per cell. How to do this without generating a value error and throwing everything off is a problem.

 

[Aladin Akyurek]

On 2002-08-29 08:01, Ruffinshot wrote:
Modified the search area to only the area I need (can't believe I missed it before)... cut my calculate time down to about 2 seconds.

The corrected formula is:
=IF(ISERROR(INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B$1:$B$44,0),2)))),"",INDIRECT("Reference!"&(ADDRESS(MATCH(I11,Reference!$B$1:$B$44,0),6))))

I'm matching a two letter code (VA,NC,MD, etc.) from Reference! column B, rows 1 through 44, to get the row, then using indirect to get figures from the columns to the right. The search area never changes. The information being pulled out is between columns B and J.

The other match I'm using is matching text with column C to get the 2 letter code to match with column B.

The only other way I can see (though I'm probably missing something) to speed it up would be to take the IF/ISERROR out so it only has to match once per cell. How to do this without generating a value error and throwing everything off is a problem.

IF the data area is fixed to B1:J44, try the following:

( 1.) Select B1:J44.
( 2.) Go to the Name Box on the Formula Bar, type Drange, and hit enter.

Now you can use one of:

=IF(ISNUMBER(MATCH(I11,INDEX(Drange,0,1),0)),VLOOKUP(I11,Drange,5,0),"")

=IF(ISNUMBER(SETV(MATCH(I11,INDEX(Drange,0,1),0))),INDEX(Drange,GETV(),5),"")

=IF(ISNA(SETV(VLOOKUP(I11,Drange,5,0))),"",GETV())

The last two use functions from the morefunc add-in.

The above formulations do not include any volatile function like ADDRESS and INDIRECT as does your corrected formula.

I thrust you can adapt the above scheme to:


The other match I'm using is matching text with column C to get the 2 letter code to match with column B,
which puzzles me.

EDIT: I added 0 to MATCH and VLOOKUP in accordance with your own formula that uses exact match.
This message was edited by aladin akyurek on 2002-08-29 12:11

 

[Ruffinshot]

Still not quite getting it. I tried the first one and couldn't get it to work. VLookup didn't seem to be cooperating. It got the right column, but it only seemed to be looking at the first row. So all I could get were the top cell in the correct column.

I'm getting that Match/Index doesn't have any volatiles so it's faster than indirect. Correct?

Re-worked the bit I've been posting:

=IF(ISNUMBER(INDEX(Reference!$F$1:$F$44,MATCH(I11,Reference!$B$1:B$44,0))),INDEX(Reference!$F$1:$F$44,MATCH(I11,Reference!$B$1:B$44,0)),"")

I tried named ranges, which is something I hadn't used before. For this I just used the normal formula. Are named ranges any better (faster), or is it just a different way of getting the same thing?

(BTW, really appreciate the help.)

 

[Aladin Akyurek]

On 2002-08-29 11:29, Ruffinshot wrote:
Still not quite getting it. I tried the first one and couldn't get it to work. VLookup didn't seem to be cooperating. It got the right column, but it only seemed to be looking at the first row. So all I could get were the top cell in the correct column.

It appears I forgot to adapt the formulas for exact match (see the edited post). So, I'd suggest that you try again.

I'm getting that Match/Index doesn't have any volatiles so it's faster than indirect. Correct?

Yes, the volatile functions prolonge the recalc times.

Re-worked the bit I've been posting:

=IF(ISNUMBER(INDEX(Reference!$F$1:$F$44,MATCH(I11,Reference!$B$1:B$44,0))),INDEX(Reference!$F$1:$F$44,MATCH(I11,Reference!$B$1:B$44,0)),"")

I see you discovered the absence of 0 in MATCH.

I tried named ranges, which is something I hadn't used before. For this I just used the normal formula. Are named ranges any better (faster), or is it just a different way of getting the same thing?

I just suggested a single name for your data area and used INDEX to access its different columns. This is much better than naming every part of the area.

(BTW, really appreciate the help.)

You're welcome.

 

[kskinne]

This may be totally off base, but instead of trying to take a cell with text in it, and extract a number, why not just format a cell so that when you type 100 in it, it is formatted to show up as 'Buy 100 Eggs'. You could do this by using a custom number format of:

"Buy" ##,### "Eggs"

Like I said maybe this is off base and won't work for what you are trying to do, i.e., maybe the text isn't always going to be Buy (insert quantity here) Eggs. Good luck anyway

kevin

 

[Ruffinshot]

So, I'd suggest that you try again.

Works. Eventually I may even figure out how. Maybe.

I just suggested a single name for your data area and used INDEX to access its different columns. This is much better than naming every part of the area.

Does it keep the range values in memory while it works? It'd make sense... wouldn't be looking it up but once.

Thanks again! Everything I do is based on this sheet, so you've just saved me a LOT of skull sweat.