Removing character from Text field

  • 0
  • 2
  • Question
  • Updated 7 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

  • 844 Points 500 badge 2x thumb

Posted 4 years ago

  • 0
  • 2
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

  • 844 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,434 Points 20k badge 2x thumb
Shorter:

Left([Field A], "-") & Right([Field A], "-")
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,434 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 Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 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.
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,434 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,992 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?
Can you provide an example of what you mean?
Discussed here
Photo of Ryan Stanford

Ryan Stanford

  • 1,992 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?
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.