Return Different Amounts of Characters from a cell

Angela Cisneros

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!

Sameer Karkhanis

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))

Angela Cisneros

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))

Leibel S

Is there a space between the Foundry ID and the Month?

Mike Wilday

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)

Angela Cisneros

No there is not always a space there.

Leibel S

Is the rest of the heat code always the same length? (Not including spaces).

Sameer Karkhanis

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))

Angela Cisneros

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))

Mike Wilday

Great use of mid to single out that number. Great job.

Angela Cisneros

Thanks. This was definitely a thinker for me and all the suggestions from y'all helped a ton! Thanks for your help!