Converting Text Into Numerical Value

jfvbms2023

I'm trying to convert the number 3 that came from the below formula into a value number instead of a text. I already tried putting VALUE in front of the LEFT function but I'm still getting an error message. Any suggestions will be appreciated.

=IF(OR(CONTAINS("Pallets", LOWER([# of Pallets/Dimensions/Weight]@row)), CONTAINS("Pallet", LOWER([# of Pallets/Dimensions/Weight]@row))), LEFT([# of Pallets/Dimensions/Weight]@row, 2), "")

Formula was used to extract the number 3 from the below text.

3 x pallets
40X48X43 = 420LBS
40X48X45 = 250 LBS
40X48X33 =150 LBS

Jgorsich

You are asking it to extract 2 characters with your left function - the 3 and the space after it. Change that to a single character and it will likely work for you.

If you think you might sometimes have multiple characters that you need to extract (like the number "13"), you'll need to nest a find() command for a defining character (likely your space, as long as you know it will ALWAYS be "3 x" and never "3x" for 3 pallets - if it might be either one, you might need to do multiple finds nested inside of a min function like this "min(find(),find(),find())" in place of your character count for your left() function. Make sure to subtract 1 from the result of the find, btw.

it might look something like this:

=IF(OR(CONTAINS("Pallets", LOWER([# of Pallets/Dimensions/Weight]@row)), CONTAINS("Pallet", LOWER([# of Pallets/Dimensions/Weight]@row))), VALUE(LEFT([# of Pallets/Dimensions/Weight]@row, MIN(FIND(" ", [# of Pallets/Dimensions/Weight]@row)) - 1)), "")

edit - note that I added the min function there, if you think you might need to trigger off of the "x" instead, or ever the word "pallets", modify it like this:

MIN(FIND(" ", [# of Pallets/Dimensions/Weight]@row),FIND("x", [# of Pallets/Dimensions/Weight]@row),FIND("pallets", [# of Pallets/Dimensions/Weight]@row))