| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.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 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 |
|
#2
Posted to microsoft.public.excel.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. "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 |
|
#3
Posted to microsoft.public.excel.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 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 |
|
#4
Posted to microsoft.public.excel.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... "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 |
|
#5
Posted to microsoft.public.excel.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... "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 |
|
#6
Posted to microsoft.public.excel.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 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 - |
|
#7
Posted to microsoft.public.excel.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 "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 - |
|
#8
Posted to microsoft.public.excel.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 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 - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help formula that returns a text string from another sheet | Excel Worksheet Functions | |||
| Pulling Year from a text string | Excel Worksheet Functions | |||
| What does "Workbook has no bound sheets" mean? | Excel Discussion (Misc queries) | |||
| What does "Workbook has no bound sheets" mean? | Excel Discussion (Misc queries) | |||
| pulling characters out of a string | Excel Worksheet Functions | |||
Linear Mode
