Is there a way to hide text following an @ sign for multiple lines?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
A multi-line text field has one or many lines with information like below:

2 x part # 1 @ $50
3 x part # 2 @ $100
3 x part # 3 @ $150

I need to display this information in a separate field, but without the @ $50,@ $100, etc, like this:

2 x part # 1 
3 x part # 2 
3 x part # 3

I used NotRight([Calculations],"="), and it works, but only when there is one line.  When there are multiple lines, it looks like this:

2 x part # 1 @ $50
3 x part # 2 @ $100
3 x part # 3 @

Is there a way to adapt the NotRight formula to work on multiple lines?
Photo of JimLieder


  • 344 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
I don't think you can do that with the default formulas available.

I'd suggest that you should break this data out into individual records (each row) and then you can put the information in different fields (columns).

That is the true power of a relational database.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
You can do this natively:

Here is the formula I used:
var Text parta = Part([text], 1, "\n");
var Text partb = Part([text], 2, "\n");
var Text partc = Part([text], 3, "\n");
var Text partd = Part([text], 4, "\n");
var Text parte = Part([text], 5, "\n");
  Left($parta, "@"), 
  Left($partb, "@"),
  Left($partc, "@"),
  Left($partd, "@"),
  Left($parte, "@")

There might be issues in the future using this formula as errant characters might get pasted into the [text] field that mess up the formula. Even this simple formula is reaching the edge of what can be done naively. Parsing is better done with JavaScript at the time of data entry.

The parsing capabilities in JavaScript will blow your mind and are only getting more awesome over time. I myself am looking forward to the look behind feature coming out in the upcoming JavaScript standard:

ES2018: RegExp Lookbehind Assertions