Do a calculation in cells with text data format

R

Ray

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
 
R

Ray

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
 
D

Dave Peterson

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.
 
R

Ray

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
 
D

Dave Peterson

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.
 
R

Ray

Dave,

Excellent! Thanks a lot.

Ray

Dave Peterson said:
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.
Click to expand...
 
Top