Converting Text Into Numerical Value
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
Answers
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!