Bob,
Here is the spreadsheet from the other post. I just copied a piece of the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns. Column B
worked fine. In column C, I wanted the words, but only got part of them.
Column D is your formula.

Thanks for your help!!

oops, just noticed, some of them have a dash - at the end, what does that
signify?

--

HTH

RP

"Dan B" wrote in message
...
Bob,
Here is the spreadsheet from the other post. I just copied a piece of the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns. Column
B
worked fine. In column C, I wanted the words, but only got part of them.
Column D is your formula.

Thanks for your help!!

I can't believe that's all it was.....although I was thinking all along that
it had to be something stupid that. How embarrassing!!

The dash......it must be signifying a negative number. This is from a data
dump out of an accounting system.

Would you have any pointers on why the formula in column C is only pulling
the first word...such as US instead of US Government Bonds?

Thanks for all your help!!



"Bob Phillips" wrote in message
...
oops, just noticed, some of them have a dash - at the end, what does that
signify?

--

HTH

RP

"Dan B" wrote in message
...
Bob,
Here is the spreadsheet from the other post. I just copied a piece of
the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns.
Column
B
worked fine. In column C, I wanted the words, but only got part of them.
Column D is your formula.

Thanks for your help!!

Dan,

If you wanjt to preserve the sign, I would use a helper column.

D1: =TRIM(IF(RIGHT(TRIM(A1))="-",LEFT(TRIM(A1),LEN(TRIM(A1))-1),A1))
E1: =--(IF(RIGHT(TRIM(A1))="-","-","")&MID((D1),FIND("~",SUBSTITUTE((D1),"
","~",
LEN(D1)-LEN(SUBSTITUTE(D1," ",""))))+1,99))

your formula only gets US as it only scans along as far as three spaces.

--

HTH

RP

"Dan B" wrote in message
...
I can't believe that's all it was.....although I was thinking all along
that
it had to be something stupid that. How embarrassing!!

The dash......it must be signifying a negative number. This is from a
data
dump out of an accounting system.

Would you have any pointers on why the formula in column C is only pulling
the first word...such as US instead of US Government Bonds?

Thanks for all your help!!



"Bob Phillips" wrote in message
...
oops, just noticed, some of them have a dash - at the end, what does
that
signify?

--

HTH

RP

"Dan B" wrote in message
...
Bob,
Here is the spreadsheet from the other post. I just copied a piece of
the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns.
Column
B
worked fine. In column C, I wanted the words, but only got part of
them.
Column D is your formula.

Thanks for your help!!

Good idea! This all gets me exactly what I needed! Thank You so Much,
Bob!!

Dan



"Bob Phillips" wrote in message
...
Dan,

If you wanjt to preserve the sign, I would use a helper column.

D1: =TRIM(IF(RIGHT(TRIM(A1))="-",LEFT(TRIM(A1),LEN(TRIM(A1))-1),A1))
E1: =--(IF(RIGHT(TRIM(A1))="-","-","")&MID((D1),FIND("~",SUBSTITUTE((D1),"
","~",
LEN(D1)-LEN(SUBSTITUTE(D1," ",""))))+1,99))

your formula only gets US as it only scans along as far as three spaces.

--

HTH

RP

"Dan B" wrote in message
...
I can't believe that's all it was.....although I was thinking all along
that
it had to be something stupid that. How embarrassing!!

The dash......it must be signifying a negative number. This is from a
data
dump out of an accounting system.

Would you have any pointers on why the formula in column C is only
pulling
the first word...such as US instead of US Government Bonds?

Thanks for all your help!!



"Bob Phillips" wrote in message
...
oops, just noticed, some of them have a dash - at the end, what does
that
signify?

--

HTH

RP

"Dan B" wrote in message
...
Bob,
Here is the spreadsheet from the other post. I just copied a piece of
the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns.
Column
B
worked fine. In column C, I wanted the words, but only got part of
them.
Column D is your formula.

Thanks for your help!!

Dan,

You gotta laugh. There was a space at the end of your data which was
throwing it!

Try this

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

--

HTH

RP

"Dan B" wrote in message
...
Bob,
Here is the spreadsheet from the other post. I just copied a piece of the
data into this one. You'll be able to see what I was trying to
accomplish.....splitting data in column A into 3 separate columns. Column
B
worked fine. In column C, I wanted the words, but only got part of them.
Column D is your formula.

Thanks for your help!!