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.

 

[Roccofan]

If your numbers are all the same number of characters and appear the same number of characters from the left or right then using MID and VALUE should get you what you need.

For Example: On September 21, 2002 I will have 234 eggs.

If you have 50 statements like the onve above and the only thing that changes are the number of eggs (represented by 3 characters) then the following should give you the number:

=VALUE(MID($A$1,35,3))

Adjust the cell reference and copy the formula and you should have what you need. If the text varies in length, I think you might be out of luck.

RF

 

[Juan Pablo González]

You could use my MSUBSITUTE function, something like this function, array entered.

{=SUBSTITUTE(MSUBSTITUTE(UPPER(A1),CHAR(ROW(A1:A26)+64)),"")," ","") + 0}

That should replace all letters and spaces from A1.

The UDF code for MSUBSTITUTE is right here

http://www.mrexcel.com/board/viewtopic.php?topic=18317&forum=2

 

[Aladin Akyurek]

On 2002-08-28 10:14, Juan Pablo G. wrote:
You could use my MSUBSITUTE function, something like this function, array entered.

{=SUBSTITUTE(MSUBSTITUTE(UPPER(A1),CHAR(ROW(A1:A26)+64)),"")," ","") + 0}

That should replace all letters and spaces from A1.

The UDF code for MSUBSTITUTE is right here

http://www.mrexcel.com/board/viewtopic.php?topic=18317&forum=2

Array-entered:

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

where E5 houses a target entry, should suffice.

 

[Joe Was]

Break up your string into three columns, the first format justify: Right the second Center and the third Left. Then you can do anything you want with the data. JSW

 

[Juan Pablo González]

On 2002-08-28 10:20, Aladin Akyurek wrote:

Array-entered:

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

where E5 houses a target entry, should suffice.

Yes, you're right Aladin... man, I really have to thank you for this Idea, this formula works very cool !

 

[Ruffinshot]

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.

 

[Juan Pablo González]

Did you even try the MSUBSTITUTE approach ? it's one cell for each record !

 

[Ruffinshot]

Msubstitute is several times longer than the alternate code I was already using (4 lines per item). I was hoping to cut it down to maybe one line of VBA code.

The spreadsheet's a separate animal and actually working now, but speed is an issue. I'm using conditionals and search to pull them out... 8,160 times on this one page. There's about 55,000 indirect/address/match bits to pull information from other sheets and that's slowing it down. Any suggestions on that would be welcome.

(The sheet takes about 4000 entries and converts them to data on the other sheets and then feeds it back to this sheet, which is why it's the size it is.)

 

[stevebausch]

"Parsing" is the term for this. Compilers do it (a lot).


Keep in mind that the Split() function of VBA 6 (Excel XP) comes in handy when parsing. And, if you want the VB6/VBA6 string functions (Split, Join, Replace, etc) in earlier versions (Excel 97), search the Microsoft site for "simulated string functions VB 5", or perhaps a subset of those search terms.

If you do use Split to help in parsing, you will want to Trim() the string, then add a space at the end of the string. That is, if you are using a space as the delimiter passed to Split, then make sure the end of the string being Split has a space at the end.

Keep in mind that (it seems to me.....) the "Simulated String Functions" on the Microsoft page have some buggy behavior; the Replace function doesn't like it if you attempt to replace one character with a multiple of the same character (endless loop).

And I think that the Split function has some weird behavior, also.

However, if you are serious about parsing strings, then you will end up using a Split function, either home-grown or built-in.