Left and Right Functions in Worksheets

[James Russell]

Hey all,

I want to analise the weight field of a Lighting Device as a value but I don't want to change the entire device library to do so.

The current Lighting Device states the weight field as a 'Text' field so an example of weight is '11.3kg' as text.

I want a worksheet formula which calculates the overall length of a field in characters, subtracts 2 places from the tail end and then converts it to a numerical value.

A similar formula from Excel/Numbers would look like:

=VALUE(LEFT(A1,LEN(A1)-2))

Where field A1 contains the subject number with kg on the end, eg. '23.56kg'

In my mind an easy translation to the header field of a worksheet with a database lookup for =DATABASE((R IN ['Lighting Device'])) would be:

=VALUE(LEFT(('Lighting Device'.'Weight'),LEN(('Lighting Device'.'Weight'))-2))

Unfortunately the LEN() and LEFT() functions do not exist in VW.

Does anyone have an idea to move forward? If not collaborative discussion time?

Cheers,

J

[michaelk]

James

I don't think there is a way inside of worksheets to do what you want...

You could script something to strip spaces and letters out of that column or change the record format and all the weight data for each fixture. Not a great solution.

The 11_ prefix in front of the field name doesn't seem to help here.

The data formatting for the weight field is a little loose. I thought it was just the imperial weights, but it looks like the metric weights are the same way. Just the ETC weights are listed in 3 imperial and 2 metric formats:

xxlb xxlbs xx Lbs

xxkg xx Kgs

So you can't just lop of the trailing x characters.

No idea to move forward that isn't a script or brute force changing of all the data.

But always up for a discussion.

mk

[James Russell]

Micheal,

I have done so with scripts in the past but was hoping for a really crafty way to do it within a worksheet cell.

I know there's some strays in the VW device library but I have an idea that I'll have to try tonight.

I'll see if I can change the Weight field in the lighting device xml to be a General integer with a trailing unit of kg, which is what I used to do in the past for appearance in my worksheet. Then seeing if VW will interpret the figures within the Light Info Record as integers with tags rather than text. It might just crash though translating the data across, guess we'll see.

Otherwise you're probably right, I might just make a script with a ForEachObject loop taking out any letter components from the Weight field string and leaving the numbers and then just run a VALUE() over the whole field in the worksheet.

I'll let you know how I go breaking things.

J