Split text without using data-text to columns

[Jambruins]

I have the following in cells
D3: -2.5 -111
D4: +2.5 +101
D5: +10.5 -114
D6: -10.5 +104
D7: -8 +100
D8: +8 -110

Is there a formula I can enter into cell H3 that will give me just -2.5?
Note that all the cells aren't necessarily in the same format (some are X.5
and some are X). Thanks

[Dave O]

This formula
=MID(D3,1,FIND(" ",D3,1)-1)
.... looks for the space in between the numbers, and pulls everything to
the left of the space.

[Elkar]

Assuming all of your cells follow the same format as Number - Space - Number,
then this will work to extract the first number before the space:

=LEFT(D3,FIND(" ",D3,1)-1)

HTH,
Elkar

"Jambruins" wrote:

> I have the following in cells
> D3: -2.5 -111
> D4: +2.5 +101
> D5: +10.5 -114
> D6: -10.5 +104
> D7: -8 +100
> D8: +8 -110
>
> Is there a formula I can enter into cell H3 that will give me just -2.5?
> Note that all the cells aren't necessarily in the same format (some are X.5
> and some are X). Thanks

[SVC]

Assuming that in cell D3 you have a space after the first number and the cell
is formatted as text, in cell H3 type the following formula:
=MID(D3,1,FIND(" ",D#,1)-1) and copy down. Note that there is a single space
between the two quotation marks.


"Jambruins" wrote:

> I have the following in cells
> D3: -2.5 -111
> D4: +2.5 +101
> D5: +10.5 -114
> D6: -10.5 +104
> D7: -8 +100
> D8: +8 -110
>
> Is there a formula I can enter into cell H3 that will give me just -2.5?
> Note that all the cells aren't necessarily in the same format (some are X.5
> and some are X). Thanks

[Jambruins]

thanks to all three of you for the help

"SVC" wrote:

> Assuming that in cell D3 you have a space after the first number and the cell
> is formatted as text, in cell H3 type the following formula:
> =MID(D3,1,FIND(" ",D#,1)-1) and copy down. Note that there is a single space
> between the two quotation marks.
>
>
> "Jambruins" wrote:
>
> > I have the following in cells
> > D3: -2.5 -111
> > D4: +2.5 +101
> > D5: +10.5 -114
> > D6: -10.5 +104
> > D7: -8 +100
> > D8: +8 -110
> >
> > Is there a formula I can enter into cell H3 that will give me just -2.5?
> > Note that all the cells aren't necessarily in the same format (some are X.5
> > and some are X). Thanks

[Jambruins]

how would I change the formula to pull everything to the right of the space?

"Dave O" wrote:

> This formula
> =MID(D3,1,FIND(" ",D3,1)-1)
> .... looks for the space in between the numbers, and pulls everything to
> the left of the space.
>
>

[RagDyer]

One way:

=MID(D3,FIND(" ",D3)+1,100)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Jambruins" <[email protected]> wrote in message
news:[email protected]...
> how would I change the formula to pull everything to the right of the
space?
>
> "Dave O" wrote:
>
> > This formula
> > =MID(D3,1,FIND(" ",D3,1)-1)
> > .... looks for the space in between the numbers, and pulls everything to
> > the left of the space.
> >
> >

[Dana DeLouis]

> ...Split text ...

If D3 does in fact have "Text", then a possible alternative...

=IMREAL(D3& "j")

HTH. :>)
--
Dana DeLouis
Win XP & Office 2003


"Jambruins" <[email protected]> wrote in message
news:[email protected]...
>I have the following in cells
> D3: -2.5 -111
> D4: +2.5 +101
> D5: +10.5 -114
> D6: -10.5 +104
> D7: -8 +100
> D8: +8 -110
>
> Is there a formula I can enter into cell H3 that will give me just -2.5?
> Note that all the cells aren't necessarily in the same format (some are
> X.5
> and some are X). Thanks