software

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(quot;~quot;,SUBSTITUTE(TRIM(A1),quot; quot;,quot;~quot;,
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;)))) 1,99)

--

HTH

RP

quot;Dan Bquot; gt; wrote in message
...
gt; Bob,
gt; Here is the spreadsheet from the other post. I just copied a piece of the
gt; data into this one. You'll be able to see what I was trying to
gt; accomplish.....splitting data in column A into 3 separate columns. Column
B
gt; worked fine. In column C, I wanted the words, but only got part of them.
gt; Column D is your formula.
gt;
gt; Thanks for your help!!
gt;
gt;
gt;
oops, just noticed, some of them have a dash - at the end, what does that
signify?

--

HTH

RP

quot;Dan Bquot; gt; wrote in message
...
gt; Bob,
gt; Here is the spreadsheet from the other post. I just copied a piece of the
gt; data into this one. You'll be able to see what I was trying to
gt; accomplish.....splitting data in column A into 3 separate columns. Column
B
gt; worked fine. In column C, I wanted the words, but only got part of them.
gt; Column D is your formula.
gt;
gt; Thanks for your help!!
gt;
gt;
gt;
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!!
quot;Bob Phillipsquot; gt; wrote in message
...
gt; oops, just noticed, some of them have a dash - at the end, what does that
gt; signify?
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt;
gt; quot;Dan Bquot; gt; wrote in message
gt; ...
gt;gt; Bob,
gt;gt; Here is the spreadsheet from the other post. I just copied a piece of
gt;gt; the
gt;gt; data into this one. You'll be able to see what I was trying to
gt;gt; accomplish.....splitting data in column A into 3 separate columns.
gt;gt; Column
gt; B
gt;gt; worked fine. In column C, I wanted the words, but only got part of them.
gt;gt; Column D is your formula.
gt;gt;
gt;gt; Thanks for your help!!
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Dan,

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

D1: =TRIM(IF(RIGHT(TRIM(A1))=quot;-quot;,LEFT(TRIM(A1),LEN(TRIM(A1))-1),A1))
E1: =--(IF(RIGHT(TRIM(A1))=quot;-quot;,quot;-quot;,quot;quot;)amp;MID((D1),FIND(quot;~quot;,SUBSTITUTE((D1),quot;
quot;,quot;~quot;,
LEN(D1)-LEN(SUBSTITUTE(D1,quot; quot;,quot;quot;)))) 1,99))

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

--

HTH

RP

quot;Dan Bquot; gt; wrote in message
...
gt; I can't believe that's all it was.....although I was thinking all along
that
gt; it had to be something stupid that. How embarrassing!!
gt;
gt; The dash......it must be signifying a negative number. This is from a
data
gt; dump out of an accounting system.
gt;
gt; Would you have any pointers on why the formula in column C is only pulling
gt; the first word...such as US instead of US Government Bonds?
gt;
gt; Thanks for all your help!!
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; oops, just noticed, some of them have a dash - at the end, what does
that
gt; gt; signify?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt;
gt; gt; quot;Dan Bquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Bob,
gt; gt;gt; Here is the spreadsheet from the other post. I just copied a piece of
gt; gt;gt; the
gt; gt;gt; data into this one. You'll be able to see what I was trying to
gt; gt;gt; accomplish.....splitting data in column A into 3 separate columns.
gt; gt;gt; Column
gt; gt; B
gt; gt;gt; worked fine. In column C, I wanted the words, but only got part of
them.
gt; gt;gt; Column D is your formula.
gt; gt;gt;
gt; gt;gt; Thanks for your help!!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Good idea! This all gets me exactly what I needed! Thank You so Much,
Bob!!

Dan
quot;Bob Phillipsquot; gt; wrote in message
...
gt; Dan,
gt;
gt; If you wanjt to preserve the sign, I would use a helper column.
gt;
gt; D1: =TRIM(IF(RIGHT(TRIM(A1))=quot;-quot;,LEFT(TRIM(A1),LEN(TRIM(A1))-1),A1))
gt; E1: =--(IF(RIGHT(TRIM(A1))=quot;-quot;,quot;-quot;,quot;quot;)amp;MID((D1),FIND(quot;~quot;,SUBSTITUTE((D1),quot;
gt; quot;,quot;~quot;,
gt; LEN(D1)-LEN(SUBSTITUTE(D1,quot; quot;,quot;quot;)))) 1,99))
gt;
gt; your formula only gets US as it only scans along as far as three spaces.
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt;
gt; quot;Dan Bquot; gt; wrote in message
gt; ...
gt;gt; I can't believe that's all it was.....although I was thinking all along
gt; that
gt;gt; it had to be something stupid that. How embarrassing!!
gt;gt;
gt;gt; The dash......it must be signifying a negative number. This is from a
gt; data
gt;gt; dump out of an accounting system.
gt;gt;
gt;gt; Would you have any pointers on why the formula in column C is only
gt;gt; pulling
gt;gt; the first word...such as US instead of US Government Bonds?
gt;gt;
gt;gt; Thanks for all your help!!
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; oops, just noticed, some of them have a dash - at the end, what does
gt; that
gt;gt; gt; signify?
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; HTH
gt;gt; gt;
gt;gt; gt; RP
gt;gt; gt;
gt;gt; gt; quot;Dan Bquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; Bob,
gt;gt; gt;gt; Here is the spreadsheet from the other post. I just copied a piece of
gt;gt; gt;gt; the
gt;gt; gt;gt; data into this one. You'll be able to see what I was trying to
gt;gt; gt;gt; accomplish.....splitting data in column A into 3 separate columns.
gt;gt; gt;gt; Column
gt;gt; gt; B
gt;gt; gt;gt; worked fine. In column C, I wanted the words, but only got part of
gt; them.
gt;gt; gt;gt; Column D is your formula.
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks for your help!!
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;