First number in a string
 

The following sample string contains the number of items = 2463.

Is there an easy way to extract that number from similar strings?

The rest of the information is not needed.

I am thinking of using the value function to text between spaces but perhaps
there is something simpler or with code.

Thanks,

Antonio
WELLP INC STK 2.463 68,24115 137.915,80 1,50 71,06000 P 139.225,82 1,54
1.310,02 0,95

First number in a string
 

I'm not 100% sure but I remember seeing a substring function in VB way
back when to extract characters from the string.

I don't think there is anyway to do it without a marco but I may be
wrong.

KaM

Antonio wrote:
The following sample string contains the number of items = 2463.

Is there an easy way to extract that number from similar strings?

The rest of the information is not needed.

I am thinking of using the value function to text between spaces but perhaps
there is something simpler or with code.

Thanks,

Antonio
WELLP INC STK 2.463 68,24115 137.915,80 1,50 71,06000 P 139.225,82 1,54
1.310,02 0,95

First number in a string
 

I suspect that a code solution is going to almost have to be used unless the
contents/format of the strings is always the same. I'm betting they are not?
In this case there are 3 spaces ahead of the first digit, would there always
be 3? Or could there just be 2? "COMPANY STK 2,463..." for example. Maybe
spaces aren't the answer, will the first digit always be in the same position
within the string (15 in this case)??

If either of those answers is no, then code is probably going to be the way
to go.

If NONE of the entry (including part of a company name like 3M) before the
first digit of interest can be a numeric digit, it's pretty easy:
parse the string and throw characters away until the first numeric digit is
encountered, build a string holding that and adding all characters following
it until a space is encountered, then you have the number including commas
and decimal points as text.

"Antonio" wrote:

The following sample string contains the number of items = 2463.

Is there an easy way to extract that number from similar strings?

The rest of the information is not needed.

I am thinking of using the value function to text between spaces but perhaps
there is something simpler or with code.

Thanks,

Antonio
WELLP INC STK 2.463 68,24115 137.915,80 1,50 71,06000 P 139.225,82 1,54
1.310,02 0,95

First number in a string
 

I think you are trying to isloate the first number in the string:
If your string is in cell F18 use:

=MID(F18,MIN(SEARCH({1,2,3,4,5,6,7,8,9},F18)),FIND ("
",F18,MIN(SEARCH({1,2,3,4,5,6,7,8,9},F18)))-MIN(SEARCH({1,2,3,4,5,6,7,8,9},F18)))

Hope this works for you.

"Antonio" wrote:

The following sample string contains the number of items = 2463.

Is there an easy way to extract that number from similar strings?

The rest of the information is not needed.

I am thinking of using the value function to text between spaces but perhaps
there is something simpler or with code.

Thanks,

Antonio
WELLP INC STK 2.463 68,24115 137.915,80 1,50 71,06000 P 139.225,82 1,54
1.310,02 0,95

First number in a string
 

On Wed, 14 Jun 2006 12:34:02 -0700, Antonio
wrote:

The following sample string contains the number of items = 2463.

Is there an easy way to extract that number from similar strings?

The rest of the information is not needed.

I am thinking of using the value function to text between spaces but perhaps
there is something simpler or with code.

Thanks,

Antonio
WELLP INC STK 2.463 68,24115 137.915,80 1,50 71,06000 P 139.225,82 1,54
1.310,02 0,95

You can use regular expressions to do this.

I have assumed that your digit separator is a "dot" and your decimal is a
"comma".

If your worksheet has to be "internationally aware", the following will not
work; but we can easily write a routine which would be.

Download and install Longre's free morefunc.xll add-in from:
http://xcell05.free.fr

Then use this formula:

=REGEX.MID(SUBSTITUTE(A1,".",""),"-?\d+(,\d*)?|-?,\d+")

to extract the first number.

If you need to extract other numbers, the function has an optional "Index"
feature. So to extract the third number, you could use:

=REGEX.MID(SUBSTITUTE(A1,".",""),"-?\d+(,\d*)?|-?,\d+",3)


--ron