Split Numerical Lookup cell

Topic Labels: Formulas
196 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hello all,

New to Airtable and I'm trying to build a product costing and BOM system.

I have worked out from searching for posts on this forum that I can split a Lookup cell based on text using the following

``````IF( FIND(",", {Product Costings}),
LEFT({Product Costings},
FIND(",", {Product Costings})-1
),
{Product Costings}
)``````

But when I try to use this same formula to split the cost cells it doesn't seem to work.

I basically have a 'Total Cost' column which is currently filled with £0.70, £1.00 and £0.11

I want to split these into separate cells as I want to specify quantity for each value. For some reason the above script will not work for numericals, and it just lists the full 0.70, 1.00, 0.11

Is there something I need to change to make this work? Or are there any other alternatives?

What I want is -

Kit Row - Combined Individual products that make up the kit - Number of each individual product needed to create the kit - Price of each individual item - Total price of each individual item (individual item cost * qty)

I've sorted this for the individual product names (BOM1) but can't make it work for the cost (BOM1 cost)

Thanks

2 Replies 2
12 - Earth

Hi,
The formula is "if value contains comma, show left part before the comma, otherwise show full value"
So, you should fix it.
But you also have second issue - LEFT is text function, it will fail with number. To convert number to text, you can add it to the empty string or wrap into concatenate. So, instead  of {FieldName} use {FieldName}&""  or CONCATENATE({FieldName})

Note that when you convert currency, result will be like '0.70' . Data stored in column as number, while currency symbol is a column property, so it is not converted to text.

4 - Data Explorer

Thanks, I actually went with a different route for this, making a separate BOMs table then linking it.