[Dba-office] Fwd: Interesting Excel problem

[Susan Harkins]

Now you're just showing off. :) Thank you Stuart -- I might actually write
this up.

Susan H.

On Tue, Apr 11, 2017 at 3:48 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Others have pointed out, Contcatenate and & add strings together, not the
> value of the
> strings. You need to use + instead end up with the sum of the two parts.
>
> That said, you can do it all without in a single step without having to go
> through  Text To
> Column:
>
> =LEFT(A1,FIND(";",A1)-1)+MID(A1,FIND(";",A1)+1,3)/16
>
>
> On 11 Apr 2017 at 13:11, Susan Harkins wrote:
>
> > Our animal care submits some weights in a strange format:
> >
> > pound;ounce
> >
> > So, the baby bobcat's weights might resemble 10;7, 14;3, 14;8, and so
> > on.
> >
> > Parsing them is no problem. But, evaluating and returning poundage in
> > decimal format is proving a challenge because I'm seeing something
> > weird along the way. Specifically, regardless of how I concatenate the
> > two values, I get unexpected results.
> >
> > I used Column To Text to parse the values using the ; character as the
> > delimiter. Pounds are in column B, the ounce value is in column C. In
> > column D, I use the formula:
> >
> > =C4/16
> >
> > to convert the ounce value into a decimal value.
> >
> > Here's where I run into trouble -- I've been unable to concatenate the
> > pound integer and the results of that formula to return
> >
> > 10.4375
> >
> > 10 is the number of pounds; .4375 is 7 ounces, expressed as a decimal.
> >
> > It's impossible to put them together!
> >
> > I first tried
> >
> > =B4&D4
> >
> > which returns
> >
> > 100.4375
> >
> > instead of 10.4375.
> >
> > I've tried CONCATENATE(). I've tried working with a text value instead
> > of the results of a formula. I've tried TRIM() and ABS() -- there's
> > something going on that I clearly don't understand.
> >
> > There's probably an easier way to get what I need -- forest for the
> > trees. But I don't understand why concatenating these two values --
> > whether value or text -- wants to add a 0 to the integer.
> >
> > Susan H.
> > _______________________________________________
> > Dba-office mailing list
> > Dba-office at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-office
> >
>
>
> _______________________________________________
> Dba-office mailing list
> Dba-office at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-office
>