Return Different Amounts of Characters from a cell
Hello Smartsheet Community,
I am trying to come up with a formula to pull the 3 or 4 characters from the beginning of a cell. The Foundry ID is what I am looking for (pictured below), but the lengths varies between 3 and 4 characters. Does anyone have any ideas on how to accomplish this?
Thanks!
Best Answers
-
Expanding on Mike's suggestion and adding the logic depending on whether there is space or not.
Basically what the below does is, checks if the 4th character is space, if it is then returns the first 3 characters as Foundary Id, otherwise returns first 4 characters as Foundary Id.
=IF(FIND(" ", [Heat Code]@row) = 4, LEFT([Heat Code]@row, 3), LEFT([Heat Code]@row, 4))
-
Thanks for the suggestions. I ended up with the following formula. Since there is no space in the heatcode, I used Isnumber to identify the if the 5th character is a number to either return 3 or 4 characters.
=IF(ISNUMBER(IFERROR(VALUE(MID([Heat Codes]@row, 5, 1)), "")), LEFT([Heat Codes]@row, 3), LEFT([Heat Codes]@row, 4))
Answers
-
Is there a space between the Foundry ID and the Month?
-
If there is always a space there, then you can use the left formula in combination of find formula to find the first space position then subtract that position by one and you'll get everything to the left of it.
Just replace "column name of code" in both parts with the name of your column and remove the brackets if your column name is one word with no spaces and doesn't end in a number.
=left([column name of code]@row, Find(" ", [Column name of code]@row)-1)
-
No there is not always a space there.
-
Is the rest of the heat code always the same length? (Not including spaces).
-
Expanding on Mike's suggestion and adding the logic depending on whether there is space or not.
Basically what the below does is, checks if the 4th character is space, if it is then returns the first 3 characters as Foundary Id, otherwise returns first 4 characters as Foundary Id.
=IF(FIND(" ", [Heat Code]@row) = 4, LEFT([Heat Code]@row, 3), LEFT([Heat Code]@row, 4))
-
Thanks for the suggestions. I ended up with the following formula. Since there is no space in the heatcode, I used Isnumber to identify the if the 5th character is a number to either return 3 or 4 characters.
=IF(ISNUMBER(IFERROR(VALUE(MID([Heat Codes]@row, 5, 1)), "")), LEFT([Heat Codes]@row, 3), LEFT([Heat Codes]@row, 4))
-
Great use of mid to single out that number. Great job.
-
Thanks. This was definitely a thinker for me and all the suggestions from y'all helped a ton! Thanks for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!