Pulling a # from a sheet bound text string
 

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 11th 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

Pulling a # from a sheet bound text string
 

=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.

"Arturo" wrote:

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 11th 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

Pulling a # from a sheet bound text string
 

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

On Apr 8, 1:01 am, Arturo wrote:
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

Pulling a # from a sheet bound text string
 

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

"BoniM" wrote:

=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.

"Arturo" wrote:

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 11th 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

Pulling a # from a sheet bound text string
 

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" wrote:

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

On Apr 8, 1:01 am, Arturo wrote:
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

Pulling a # from a sheet bound text string
 

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

On Apr 8, 12:41*pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:01 am, Arturo wrote:
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- Hide quoted text -

- Show quoted text -

Pulling a # from a sheet bound text string
 

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" wrote:

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

On Apr 8, 12:41 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:01 am, Arturo wrote:
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- Hide quoted text -

- Show quoted text -

Pulling a # from a sheet bound text string
 

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

On Apr 8, 1:34*pm, Arturo wrote:
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" wrote:
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

On Apr 8, 12:41 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:01 am, Arturo wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Pulling a # from a sheet bound text string
 

My apologies,
Decaf wasnt helping this morning€¦
I pointed it to the wrong cell ref other than A1
Thank you for your help.
Best,
Arturo


"BoniM" wrote:

=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.

"Arturo" wrote:

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 11th 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

Pulling a # from a sheet bound text string
 

My apologies,
Decaf wasnt helping this morning€¦
I pointed it to the wrong cell ref other than A1
Thank you for your help.
Best,
Arturo

"Pete_UK" wrote:

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

On Apr 8, 1:34 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 12:41 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:01 am, Arturo wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Pulling a # from a sheet bound text string
 

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

Pete

On Apr 8, 2:47 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:34 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 12:41 pm, Arturo wrote:
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" wrote:
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

On Apr 8, 1:01 am, Arturo wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -