# Return Different Amounts of Characters from a cell

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

No there is not always a space there.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!