I’m trying to figure out how to truncate characters from a cell but am having troubles.
For most of the data, it’s easy.
ex. “13: FA16” with the function
=RIGHT([Launch Semester]@row, 4)
yields “FA16” which is exactly what we’re looking for.
However, some of the cells have additional data like “00: Unknown” and returning the last 4 characters, “nown” isn’t useful.
So, I’ve tried this formula
=RIGHT([Launch Semester]@row, LEN([Launch Semester]@row – 4))
based on this article and I’m getting an error, “INVALID OPERATION.” The problem is, I don’t know if I’m getting this error because I’m using tutorial information for Excel or if it’s because I’ve messed something up.
I believe it is the LEN formula because when I use the formula below, I still get the error
=LEN([Launch Semester]89 – 4)
But when I change it to this, I do get the right number!
=(LEN([Launch Semester]@row)) – 4
And there we go… here’s the working formula:
=RIGHT([Launch Semester]@row, (LEN([Launch Semester]@row)) – 4)
Now, “00: Unknown” returns “Unknown” which is exactly what we’re looking for.
I’m now looking to make updates to a formula that currently displays the first and last name of the designer to only show the first name (Lead ID column). This is the current formula that displays both names.
1.
=INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))I found an interesting post that might help https://community.smartsheet.com/discussion/73301/remove-space-and-the-letter-after, but it is taking some time to interpret the formula they use.
2.
=IFERROR(LEFT([First Name]@row, FIND(" ", [First Name]@row) - 1), [First Name]@rowUnderstanding the FIND function is critical to this formula since it is new to me.
https://help.smartsheet.com/function/find
I believe I have a better understanding now. The formula above was recommended as a refinement to the original formula because the initial post asked about an error in one of the cells that did not have a “space” with an initial. It was just a first name which is the desired output. This original formula does work, it just doesn’t cover all of the potential instances of the data set.
3.
=LEFT([First Name]@row, FIND(" ", [First Name]@row) - 1)For now, I should focus on this original formula as a way to build my own. I know the base formula isn’t set to output exactly what I want, but it should still output something closer to what I’m looking for.
4.
=LEFT(=INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0)), FIND(" ", =INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0)) - 1)OK, I realized I have some unnecessary “=” before INDEX, but removing them yields an “#INVALID OPERATION” error.
5.
=LEFT(INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0)), FIND(" ", INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0)) - 1))At this point, I don’t know why I’m getting an error. Is it because I didn’t fully tune the formula to my desired output? Is it because Smartsheet cannot handle INDEX-MATCH functions within this new formula?
4/5 Update: I met with Alison and she mentioned three great options:
I think I’m going to start with the nested statements because that is something I can try on my own. While not as elegant as some of the draft formulas above, it could work.
We have success! It may not look pretty, but it works!
Changing “Lead ID” column formula from
6.
=INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))to
7.
=IF((INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))) = "Kent Matsueda", "Kent", IF((INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))) = "Chris Gamrat", "Chris", IF((INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))) = "Jon Hughes", "Jon", IF((INDEX({Lead ID}, MATCH([Course #]@row, {Course number}, 0))) = "Dean Croll", "Dean", "New Designer"))))Changing “Backup ID” column formula from
8.
=INDEX({Backup ID}, MATCH([Course #]@row, {Course number}, 0))to
9.
=IF((INDEX({Backup ID}, MATCH([Course #]@row, {Course number}, 0))) = "Kent Matsueda", "Kent", IF((INDEX({Backup ID}, MATCH([Course #]@row, {Course number}, 0))) = "Chris Gamrat", "Chris", IF((INDEX({Backup ID}, MATCH([Course #]@row, {Course number}, 0))) = "Jon Hughes", "Jon", IF((INDEX({Backup ID}, MATCH([Course #]@row, {Course number}, 0))) = "Dean Croll", "Dean", "New Designer"))))4/6 Update: I realized yesterday after making the updates that my changes broke filters we had set up. If it broke filters, it might have broken other dependencies. I reverted changes and will look into creating new columns to store first name information as much as I don’t want to do that.
New column: “Lead ID:”
10.
=IF([Lead ID]@row = "Kent Matsueda", "Kent", IF([Lead ID]@row = "Chris Gamrat", "Chris", IF([Lead ID]@row = "Jon Hughes", "Jon", IF([Lead ID]@row = "Dean Croll", "Dean", [Lead ID]@row))))New column: “Backup ID:”
11.
=IF([Back up ID Contact]@row = "Kent Matsueda", "Kent", IF([Back up ID Contact]@row = "Chris Gamrat", "Chris", IF([Back up ID Contact]@row = "Jon Hughes", "Jon", IF([Back up ID Contact]@row = "Dean Croll", "Dean", [Back up ID Contact]@row))))New column: “Project ID:”
12.
=IF([Dev/Rev Project ID]@row = "Kent Matsueda", "Kent", IF([Dev/Rev Project ID]@row = "Chris Gamrat", "Chris", IF([Dev/Rev Project ID]@row = "Jon Hughes", "Jon", IF([Dev/Rev Project ID]@row = "Dean Croll", "Dean", [Dev/Rev Project ID]@row))))