Pulling a # from a sheet bound text string

[Arturo]

I have several rows with the following beginning text as follows:

There are 1 projects ABC…
There are 14 projects ABC…
There are 140 projects ABC…
There are 1400 projects ABC…

Been experimenting with MID, LEFT, RIGHT to no avail where I cannot figure
out how to pull the number starting at the 11’th place out. That number
could be from 1 to 1000. I tripping over the varying number of spaces any
number in a cell might be as depicted above. The text flowing ‘project’,
'ABC...' varies also… Any direction as how to combine functions would be
appreciated.

Sincerely,
Arturo

 

[BoniM]

=MID(A1,11,FIND(" ",A1,11)-11)
finds the location of the first space after the 11th character and subtracts
11 from that to return the correct number of characters.

 

[Pete_UK]

You can extract the number with this formula:

=MID(A1,11,SEARCH(" ",A1,11)-11)*1

assuming the text is in A1. Copy down for the other numbers.

Hope this helps.

Pete

 

[Arturo]

When I use this, #Value! results.
What I'm trying to extract is the number housed in that string; 1 or 14 or
140...

 

[Arturo]

When I use this, #Value! results.
What I'm trying to extract is the number housed in that string; 1 or 14 or
140...

 

[Pete_UK]

Are you sure that the first number is the 11th character? Do you have
any double spaces which might make it the 12th character? Did you
remember to change the A1 reference to suit your data?

Pete

 

[Arturo]

In the four examples, by my count the 1 is the eleventh char.
I did chance the A1 to the correct ref.
Tried counting w/out the single spaces.
Not sure where I'm going wrong
A

 

[Pete_UK]

Well it worked for me in my test, and BoniM came up with a very
similar formula.

In my formula you can omit the *1 at the end and you should get a text
result rather than #VALUE, so you should be able to see what is being
returned.

Failing that, copy your exact formula from the sheet and paste it into
the newsgroups so that we can see if you have made a mistake in typing
it in.

Hope this helps.

Pete

 

[Arturo]

My apologies,
Decaf wasn’t helping this morning…
I pointed it to the wrong cell ref other than A1
Thank you for your help.
Best,
Arturo

 

[Arturo]

My apologies,
Decaf wasn’t helping this morning…
I pointed it to the wrong cell ref other than A1
Thank you for your help.
Best,
Arturo

 

[Pete_UK]

That's quite allright - I did suggest that to you earlier.

Pete