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

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

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

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

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

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

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

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

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

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