Pulling a # from a sheet bound text string  
Author Message
lestat





PostPosted: Mon Apr 07 19:24:00 CDT 2008 Top

worksheet functions >> 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 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

Excel143  
 
 
BoniM





PostPosted: Mon Apr 07 19:24:00 CDT 2008 Top

worksheet functions >> 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.



> 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
 
 
Pete_UK





PostPosted: Mon Apr 07 19:29:05 CDT 2008 Top

worksheet functions >> 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


> 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

 
 
Arturo





PostPosted: Tue Apr 08 06:41:00 CDT 2008 Top

worksheet functions >> 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...



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

>
> > 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
 
 
Arturo





PostPosted: Tue Apr 08 06:41:01 CDT 2008 Top

worksheet functions >> 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...



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

> > 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
>
>
 
 
Pete_UK





PostPosted: Tue Apr 08 07:13:10 CDT 2008 Top

worksheet functions >> 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


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

> 140...
>
>
>

> > You can extract the number with this formula:
>
> > =3DMID(A1,11,SEARCH(" ",A1,11)-11)*1
>
> > assuming the text is in A1. Copy down for the other numbers.
>
> > Hope this helps.
>
> > Pete
>

> > > 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 fi=
gure
> > > out how to pull the number starting at the 11'th place out. =A0That nu=
mber
> > > could be from 1 to 1000. =A0I tripping over the varying number of spac=
es any
> > > number in a cell might be as depicted above. =A0The text flowing 'proj=
ect',
> > > 'ABC...' varies also... =A0Any direction as how to combine functions w=
ould be
> > > appreciated.
>
> > > Sincerely,
> > > Arturo- Hide quoted text -
>
> - Show quoted text -

 
 
Arturo





PostPosted: Tue Apr 08 07:34:00 CDT 2008 Top

worksheet functions >> 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




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

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

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

> > > > 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 -
>
>
 
 
Pete_UK





PostPosted: Tue Apr 08 07:54:49 CDT 2008 Top

worksheet functions >> 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


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

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

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

> > > > You can extract the number with this formula:
>
> > > > =3DMID(A1,11,SEARCH(" ",A1,11)-11)*1
>
> > > > assuming the text is in A1. Copy down for the other numbers.
>
> > > > Hope this helps.
>
> > > > Pete
>

> > > > > 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 canno=
t figure
> > > > > out how to pull the number starting at the 11'th place out. =A0Tha=
t number
> > > > > could be from 1 to 1000. =A0I tripping over the varying number of =
spaces any
> > > > > number in a cell might be as depicted above. =A0The text flowing '=
project',
> > > > > 'ABC...' varies also... =A0Any direction as how to combine functio=
ns would be
> > > > > appreciated.
>
> > > > > Sincerely,
> > > > > Arturo- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

 
 
Arturo





PostPosted: Tue Apr 08 08:15:01 CDT 2008 Top

worksheet functions >> Pulling a # from a sheet bound text string 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




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

>
> > 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
 
 
Arturo





PostPosted: Tue Apr 08 08:47:03 CDT 2008 Top

worksheet functions >> Pulling a # from a sheet bound text string 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



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

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

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

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

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

> > > > > > 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 -
>
>
 
 
Pete_UK





PostPosted: Tue Apr 08 08:51:27 CDT 2008 Top

worksheet functions >> Pulling a # from a sheet bound text string That's quite allright - I did suggest that to you earlier.

Pete


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

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

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

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

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

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

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