Need help with this formula...

D

Dan B

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
 
D

Dave Peterson

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

Bob Phillips

=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)-LEN(SUBSTITUTE(A7,"
",""))))+1,99)
 
D

Dan B

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
 
D

Dave Peterson

Try Bob's suggestion.

Dan said:
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
Click to expand...
 
D

Dave Peterson

An unfortunate line break in Bob's formula:

=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)
-LEN(SUBSTITUTE(A7," ",""))))+1,99)

(all one cell)
 
B

Bob Phillips

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 said:
I get a #Value! error with Bob's
Click to expand...
 
D

Dan B

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 said:
It might be NG wrap-around. Try this version and feed back

=--MID(A7,FIND("~",SUBSTITUTE(A7," ","~",LEN(A7)-
LEN(SUBSTITUTE(A7," ",""))))+1,99)
Click to expand...
 
D

Dave Peterson

Does your data entry cell really have a space in it?

If it does, you may want to post the formula you used.
 
B

Bob Phillips

Rather than selecting the cell and pasting it, select the cell, and go to
the formula bar and paste it. Just a thought.
 
D

Dan B

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

Bob Phillips

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 said:
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!!
Click to expand...
 
D

Dave Peterson

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

Dan B

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
 
B

Bob Phillips

Why don't you post me the workbook - subject - Excel - WsF - Dan B?

--
HTH

RP
Dan B said:
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
Click to expand...
 
Top