Do a calculation in cells with text data format

I have a few columns of cells having a mixed data format of number and text. 
Is it possible to convert the first row of numbers in text data format for 
further calculation? Your guidance to accomplish it is appreciated.

Thanks,

Ray 


0
12/12/2005 2:37:39 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
526 Views

Similar Articles

[PageSpeed] 17

Example?

-- 

Regards,

Peo Sjoblom

"Ray" <NoSpam-ZQLi@GMail.com> wrote in message
news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> I have a few columns of cells having a mixed data format of number and
text.
> Is it possible to convert the first row of numbers in text data format for
> further calculation? Your guidance to accomplish it is appreciated.
>
> Thanks,
>
> Ray
>
>


0
terre081 (3244)
12/12/2005 3:17:36 PM
Peo,

Below is an example.

A1                                A2        A3
67                                78        =A1+A2
Will be discontinued.

"67" and "Will be discontinued." are on two rows of one cell.

Your advice is appreciated.

Ray

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> Example?
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
> "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> I have a few columns of cells having a mixed data format of number and
> text.
>> Is it possible to convert the first row of numbers in text data format 
>> for
>> further calculation? Your guidance to accomplish it is appreciated.
>>
>> Thanks,
>>
>> Ray
>>
>>
>
> 


0
12/12/2005 3:28:12 PM
If you use alt-enter to force that new line in the cell, you could use:

=--LEFT(A1,FIND(CHAR(10),A1)-1)

The =left() portion returns the value as a string.  The -- converts that string
to a number.



Ray wrote:
> 
> Peo,
> 
> Below is an example.
> 
> A1                                A2        A3
> 67                                78        =A1+A2
> Will be discontinued.
> 
> "67" and "Will be discontinued." are on two rows of one cell.
> 
> Your advice is appreciated.
> 
> Ray
> 
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> > Example?
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> >> I have a few columns of cells having a mixed data format of number and
> > text.
> >> Is it possible to convert the first row of numbers in text data format
> >> for
> >> further calculation? Your guidance to accomplish it is appreciated.
> >>
> >> Thanks,
> >>
> >> Ray
> >>
> >>
> >
> >

-- 

Dave Peterson
0
petersod (12005)
12/12/2005 4:02:24 PM
Dave,

Many thanks for your suggestion that works correctly provided the cell does 
have two rows.  It not, it does not work.  As the cell may have one or two 
rows, is it possible to check the condition before applied your statement?

Ray

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:439D9F10.AD38A7EC@verizonXSPAM.net...
> If you use alt-enter to force that new line in the cell, you could use:
>
> =--LEFT(A1,FIND(CHAR(10),A1)-1)
>
> The =left() portion returns the value as a string.  The -- converts that 
> string
> to a number.
>
>
>
> Ray wrote:
>>
>> Peo,
>>
>> Below is an example.
>>
>> A1                                A2        A3
>> 67                                78        =A1+A2
>> Will be discontinued.
>>
>> "67" and "Will be discontinued." are on two rows of one cell.
>>
>> Your advice is appreciated.
>>
>> Ray
>>
>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
>> > Example?
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
>> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> >> I have a few columns of cells having a mixed data format of number and
>> > text.
>> >> Is it possible to convert the first row of numbers in text data format
>> >> for
>> >> further calculation? Your guidance to accomplish it is appreciated.
>> >>
>> >> Thanks,
>> >>
>> >> Ray
>> >>
>> >>
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
12/12/2005 4:22:52 PM
One way is to just "add" that line feed in the string you're searching through:

=--LEFT(A1&CHAR(10),FIND(CHAR(10),A1&CHAR(10))-1)

If the cell already has one (or lots), it won't hurt.  If it doesn't, then the
formula will find the one we added.

Ray wrote:
> 
> Dave,
> 
> Many thanks for your suggestion that works correctly provided the cell does
> have two rows.  It not, it does not work.  As the cell may have one or two
> rows, is it possible to check the condition before applied your statement?
> 
> Ray
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:439D9F10.AD38A7EC@verizonXSPAM.net...
> > If you use alt-enter to force that new line in the cell, you could use:
> >
> > =--LEFT(A1,FIND(CHAR(10),A1)-1)
> >
> > The =left() portion returns the value as a string.  The -- converts that
> > string
> > to a number.
> >
> >
> >
> > Ray wrote:
> >>
> >> Peo,
> >>
> >> Below is an example.
> >>
> >> A1                                A2        A3
> >> 67                                78        =A1+A2
> >> Will be discontinued.
> >>
> >> "67" and "Will be discontinued." are on two rows of one cell.
> >>
> >> Your advice is appreciated.
> >>
> >> Ray
> >>
> >> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> >> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
> >> > Example?
> >> >
> >> > --
> >> >
> >> > Regards,
> >> >
> >> > Peo Sjoblom
> >> >
> >> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
> >> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
> >> >> I have a few columns of cells having a mixed data format of number and
> >> > text.
> >> >> Is it possible to convert the first row of numbers in text data format
> >> >> for
> >> >> further calculation? Your guidance to accomplish it is appreciated.
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Ray
> >> >>
> >> >>
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
12/12/2005 6:16:43 PM
Dave,

Excellent!  Thanks a lot.

Ray

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:439DBE8B.2F2E1345@verizonXSPAM.net...
> One way is to just "add" that line feed in the string you're searching 
> through:
>
> =--LEFT(A1&CHAR(10),FIND(CHAR(10),A1&CHAR(10))-1)
>
> If the cell already has one (or lots), it won't hurt.  If it doesn't, then 
> the
> formula will find the one we added.
>
> Ray wrote:
>>
>> Dave,
>>
>> Many thanks for your suggestion that works correctly provided the cell 
>> does
>> have two rows.  It not, it does not work.  As the cell may have one or 
>> two
>> rows, is it possible to check the condition before applied your 
>> statement?
>>
>> Ray
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:439D9F10.AD38A7EC@verizonXSPAM.net...
>> > If you use alt-enter to force that new line in the cell, you could use:
>> >
>> > =--LEFT(A1,FIND(CHAR(10),A1)-1)
>> >
>> > The =left() portion returns the value as a string.  The -- converts 
>> > that
>> > string
>> > to a number.
>> >
>> >
>> >
>> > Ray wrote:
>> >>
>> >> Peo,
>> >>
>> >> Below is an example.
>> >>
>> >> A1                                A2        A3
>> >> 67                                78        =A1+A2
>> >> Will be discontinued.
>> >>
>> >> "67" and "Will be discontinued." are on two rows of one cell.
>> >>
>> >> Your advice is appreciated.
>> >>
>> >> Ray
>> >>
>> >> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> >> news:uskDs8y$FHA.2708@TK2MSFTNGP12.phx.gbl...
>> >> > Example?
>> >> >
>> >> > --
>> >> >
>> >> > Regards,
>> >> >
>> >> > Peo Sjoblom
>> >> >
>> >> > "Ray" <NoSpam-ZQLi@GMail.com> wrote in message
>> >> > news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl...
>> >> >> I have a few columns of cells having a mixed data format of number 
>> >> >> and
>> >> > text.
>> >> >> Is it possible to convert the first row of numbers in text data 
>> >> >> format
>> >> >> for
>> >> >> further calculation? Your guidance to accomplish it is appreciated.
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Ray
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
12/14/2005 5:05:16 PM
Reply:

Similar Artilces: