Formula shows UNPARSABLE

I am trying to create a formula, and I keep ending up with unparsable. The name of the field I am accessing is called "Deliverable Number". Now For example, this field contains 2 characters I want to REMOVE

Original: DEL #10 - TO Portal Strategy/Solution

Desired: DEL 10 TO Portal Strategy/Solution

As you can see the "#" has been removed. Now there MAY be instances where other file would be named DEL#10 - TO Portal Strategy/Solution. In those cases I would need the # not only to be removed, but also to be replaced by a space.

As you see, the other part of the formula requires the "- " to be removed.

This is what I came up with, and it is not quite working:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Deliverable Number]@row, "DEL#", "DEL "), "DEL #", "DEL "), " - ", " "))

Find more posts tagged with

Formulas

Accepted answers

heyjay

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Deliverable Number]@row, "DEL#", "DEL "), "DEL #", "DEL "), " - ", " "), " (TDP)", "")

Just wrap the formula in another SUBSTITUTE.

=SUBSTITUTE(old formula,"old text","new text)

All comments

Bruce Case

I did it this way. Then just continued to add the possible Scaranos that needed to be changed.

=SUBSTITUTE(SUBSTITUTE([Deliverable Number]@row, "DEL#10", "DEL 10"), "DEL #10", "DEL10")

Good Luck. Have a great day!

heyjay

There is no TRIM function in Smartsheet, thats why you are receiving that error.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Deliverable Number]@row, "DEL#", "DEL "), "DEL #", "DEL "), " - ", " ")

Sherry Fox

@Bruce Case ,

Your formula still has the " - " which I need to simply replace with " ". But your help is appreciated.

Sherry Fox

@heyjay ,

That is great, but I just found another variable I had not considered.

DEL #13 - Technical Direction Plan (TDP)

Your formula makes this:

DEL 13 Technical Direction Plan (TDP)

But we need to FURTHER revise, so it will look like this:

DEL 13 Technical Direction Plan

So how can we revise your formula so that if there is " (TDP)" in the formula, it is removed. It that is not there, than nothing else changes.

Bruce Case

Hi @Sherry Fox

Just keep adding to the end of the format with what to look for, and what to change it to.

Format: , "DEL #10", "DEL10")

Example: ,"TDP", ""

heyjay

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Deliverable Number]@row, "DEL#", "DEL "), "DEL #", "DEL "), " - ", " "), " (TDP)", "")

Just wrap the formula in another SUBSTITUTE.

=SUBSTITUTE(old formula,"old text","new text)

Sherry Fox

@heyjay ,

That is perfect, Thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.