Removing character from Text field

  • 0
  • 2
  • Question
  • Updated 5 months ago
  • Answered

I have a text field [Field A]. I would like to create a text formula field [Field B] that removes the character "-" from  [Field A] if [Field A] contains the character "-".

[Field A] = "abc-d"  [Field B] = "abcd"

Thanks

Photo of rocketc

rocketc

  • 824 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Try this.  I was testing with a data entry field called [Dash text]

Part([Dash text], 1,"-") & Part([Dash text], 2,"-")
Photo of rocketc

rocketc

  • 824 Points 500 badge 2x thumb
You do man! Thank you Mark
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Shorter:

Left([Field A], "-") & Right([Field A], "-")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
But what if there is no dash present?  Then the text would get doubled up.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
The poster did not mention that he did not want doubled up text in the odd ball case there was no dash. I consider the case of doubled up text a valuable bonus behavior.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
That was trivial.

You should have been impressed with this post:

https://quickbase-community.intuit.com/questions/1282756

I have additional versions of this where I generalize the solution so that *any* summary of child records can be displayed in a field of the parent report. I am working on making the new solution dead simple to reuse.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Yes, that question always comes up on the forum.  A generalized solution would be helpful to the Community.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
FWIW, I am not through with my refactoring yet but I posted a more recent version that is more compact and where all the parameters are floated to the top of the script:

https://quickbase-community.intuit.com/questions/1282756

Also there is now a template to summarize the child field to the parent.
Photo of Taylor

Taylor

  • 52 Points
Another way to avoid doubling up of text:

If(not Contains([field],"-"), [field],
Left([field],"-")& Right([field],"-"))
Photo of Ryan Stanford

Ryan Stanford

  • 1,408 Points 1k badge 2x thumb
This solution is exactly what I need, but I need to run it multiple times, for different special characters. How could I do that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Can you provide an example of what you mean?
Discussed here
Photo of Ryan Stanford

Ryan Stanford

  • 1,408 Points 1k badge 2x thumb
Sorry about that, I responded to a similar post... which Alex linked already... It's regarding filtering out characters:

Part([Project Name],1," !@#$%^&*';:?/><")

My question is this... will this one line of code filter out all special characters listed, no matter how many times they are in the string? or do I need to add possible:

Part([Project Name],2," !@#$%^&*';:?/><") etc?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
No, the Part function will locate the nth part of a string as delimited by the delimiter characters.

So you will need

a formula like
List "", 
Part([Project Name],1," !@#$%^&*';:?/><"),
Part([Project Name],2," !@#$%^&*';:?/><"),
Part([Project Name],3," !@#$%^&*';:?/><"),

etc

Part([Project Name],10," !@#$%^&*';:?/><"))

for as many "Parts" as your data might be expected to have.