Strip Text From Worksheet

[Steven Morgan]

Searched the forum and couldn't find anything. I have a bunch of default instruments in spotlight that bring in some numerical data as text. ( 93lbs instead of 93). 

 

Rather than changing all of my records is there a way to strip out a string of text from a worksheet column? If I could remove 'lbs' and 'watts' and return only numerical data I would be in good shape.

 

Thanks!

Edited November 9, 2018 by stevenmorgan94

[Pat Stanford]

Try Value(SubString(Cell Reference Containing Text Data, Delimiter, Position))

 

If B3 Contains 93lbs, the cell containing

 

=Value(Substring(B3, 'lbs', 1))

 

Should display as 93 formatted as whatever number type you want.

 

[Steven Morgan]

Thanks Pat! Kickass class this week at VWX Summit.

[Steven Morgan]

Digging this one back up. 

 

I am trying to get the total weight of this column. I am able to strip away the 'lb' using Pat's formula above, however I can't total the column. 

 

I have tried:

=SUM(G4.1..G4.4) 

However I don't think you can select sub-rows using the range symbol.

 

Also tried:

=Value(Substring(G4, 'lb', 1))

Obviously it just returns the '4.00' which is the count of the column. I can't find a way to only apply that to sub-rows.

 

Any ideas?

[michaelk]

Is this with spotlight instrument objects?

[Pat Stanford]

the 'Lighting Device'.'Weight' field is text not a number that is why it won't add up.

 

If you are OK with not being able to change the weight from the worksheet try using =Value(Substring('Lighting Device'.'Weight', ' ' ,1))  Note that there is a space between the single quotes after the field name.

 

If you want to be able to make changes from the worksheet you will have to use two columns.  Put the Record.Field into one cell and then reference that cell in the Substring call.

 

You can use the Format Cell Trailer to add the 'lb' back into the display if you want.

[markdd]

@Pat Stanford

 

Can you use the Substring to strip all text from a field. Sometimes I have Watts wrongly abbreviated as a lower case W. It would be great to remove either 'W' or 'w" or 'kW"

 

Thanks

 

Mark

[Pat Stanford]

Like all things, it depends.

 

If the data is coming from a record.field, then substring will not help.

 

If the data is just text then maybe, because you have to have some kind of a delimiting character.

 

Would a script to edit the data attached to the objects be a better option?

 

Tell me more about how you need your data fixed.  Maybe in a new thread.