|
Need help with this formula...
Hi,
My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks |
Need help with this formula...
If your data is in A1, try
=MID(A1,SEARCH("US Government Bonds",A1)+20,255) "Dan B" wrote: Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks |
Need help with this formula...
=--MID(A7,SEARCH("us government bonds",A7)+20,255)
=FIND(" ",A7) finds the first space (directly before the "US Government...") You want to find the last space character (or use a different method). Dan B wrote: Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)-LEN(SUBSTITUTE(A7,"
",""))))+1,99) -- HTH RP "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks |
Need help with this formula...
Ok...I messed up....All of the lines don't say US Government Bonds....I was
just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks |
Need help with this formula...
Try Bob's suggestion.
Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
I get a #Value! error with Bob's
"Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
An unfortunate line break in Bob's formula:
=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7) -LEN(SUBSTITUTE(A7," ",""))))+1,99) (all one cell) Dan B wrote: I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
It might be NG wrap-around. Try this version and feed back
=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
I got the same thing. Something is wrapping.....when I paste the formula in
a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
Does your data entry cell really have a space in it?
If it does, you may want to post the formula you used. Dan B wrote: I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
Rather than selecting the cell and pasting it, select the cell, and go to
the formula bar and paste it. Just a thought. -- HTH RP "Dan B" wrote in message ... I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
Here is the formula:
=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)-LEN(SUBSTITUTE(A7," ",""))))+1,99) I noticed that it is putting a ' before the = sign and before the part that is splitting to the next row. I have tried removing the ' and putting the formula all in one row. That's when I get the #Value! error. It seems like it is treating the formula as text. This file is actually a .txt file opened in Excel. I copied the data to a new, blank spreadsheet and I got the same results. I also tried manually typing in the formula instead of copy/paste. I still got the #Value! error. Sorry to be a pain, but I appreciate the help!! "Dave Peterson" wrote in message ... Does your data entry cell really have a space in it? If it does, you may want to post the formula you used. Dan B wrote: I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
yeah, I tried that....I got the same error. Weird!!
"Bob Phillips" wrote in message ... Rather than selecting the cell and pasting it, select the cell, and go to the formula bar and paste it. Just a thought. -- HTH RP "Dan B" wrote in message ... I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson |
Need help with this formula...
Reformat the cell as General, then copy the formula in. Make sure there is a
space between the quotes after the third A7. -- HTH RP "Dan B" wrote in message ... Here is the formula: =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)-LEN(SUBSTITUTE(A7," ",""))))+1,99) I noticed that it is putting a ' before the = sign and before the part that is splitting to the next row. I have tried removing the ' and putting the formula all in one row. That's when I get the #Value! error. It seems like it is treating the formula as text. This file is actually a .txt file opened in Excel. I copied the data to a new, blank spreadsheet and I got the same results. I also tried manually typing in the formula instead of copy/paste. I still got the #Value! error. Sorry to be a pain, but I appreciate the help!! "Dave Peterson" wrote in message ... Does your data entry cell really have a space in it? If it does, you may want to post the formula you used. Dan B wrote: I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
Paste this in the Formula bar:
=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7) -LEN(SUBSTITUTE(A7," ",""))))+1,99) Copy and paste from the newsgroup message. It worked for me and I'm sure it worked for Bob. And your data is really in A7, right? Dan B wrote: yeah, I tried that....I got the same error. Weird!! "Bob Phillips" wrote in message ... Rather than selecting the cell and pasting it, select the cell, and go to the formula bar and paste it. Just a thought. -- HTH RP "Dan B" wrote in message ... I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
I tried that again....same error. The data is in A7. I tried copying data
to a new sheet again....didn't help. At this point, I think I will just accept that something in my spreadsheet is screwed up. Thanks a lot for all the help. I do appreciate it. Dan "Dave Peterson" wrote in message ... Paste this in the Formula bar: =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7) -LEN(SUBSTITUTE(A7," ",""))))+1,99) Copy and paste from the newsgroup message. It worked for me and I'm sure it worked for Bob. And your data is really in A7, right? Dan B wrote: yeah, I tried that....I got the same error. Weird!! "Bob Phillips" wrote in message ... Rather than selecting the cell and pasting it, select the cell, and go to the formula bar and paste it. Just a thought. -- HTH RP "Dan B" wrote in message ... I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
Need help with this formula...
Why don't you post me the workbook - subject - Excel - WsF - Dan B?
-- HTH RP "Dan B" wrote in message ... I tried that again....same error. The data is in A7. I tried copying data to a new sheet again....didn't help. At this point, I think I will just accept that something in my spreadsheet is screwed up. Thanks a lot for all the help. I do appreciate it. Dan "Dave Peterson" wrote in message ... Paste this in the Formula bar: =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7) -LEN(SUBSTITUTE(A7," ",""))))+1,99) Copy and paste from the newsgroup message. It worked for me and I'm sure it worked for Bob. And your data is really in A7, right? Dan B wrote: yeah, I tried that....I got the same error. Weird!! "Bob Phillips" wrote in message ... Rather than selecting the cell and pasting it, select the cell, and go to the formula bar and paste it. Just a thought. -- HTH RP "Dan B" wrote in message ... I got the same thing. Something is wrapping.....when I paste the formula in a cell, it splits it and puts the last third part of the formula in the next row. When pasting directly into the function bar, I get the #Value! error. "Bob Phillips" wrote in message ... It might be NG wrap-around. Try this version and feed back =--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)- LEN(SUBSTITUTE(A7," ",""))))+1,99) -- HTH RP "Dan B" wrote in message ... I get a #Value! error with Bob's "Dave Peterson" wrote in message ... Try Bob's suggestion. Dan B wrote: Ok...I messed up....All of the lines don't say US Government Bonds....I was just showing data layout. I should have been more specific. Every row says something different in place of the US Government.... Sorry about that. Any other ideas? Thanks "Dan B" wrote in message ... Hi, My data is as follows, all in one cell: 10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21 There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7))) My result is 9,423.52. Its not pulling the whole number. What do I need to change in the formula? Thanks -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com