Formula to replace all instances of "X' with "Y" in a text field

  • 1
  • 1
  • Question
  • Updated 4 years ago
  • Answered

One for the formula field experts who like a challenge!

I've been trying to write a formula field to replace all instances of "X" with "Y" within the source multiline text field - where X and Y are strings.

My specific use case is to replace all instances of "£" with "£" (see http://www.w3schools.com/html/html_entities.asp for background as to why I need to do this if interested - the data feeds out of Quickbase to elsewhere, hence why I need to convert it)

The input field can be any length, and quite long, and can therefore contain many instances of the £ sign.

However, realistically for the purposes of the formula, it would probably be safe to assume there would be no more than 10 instances of "X" in the input field.

Ideally I need the solution to be based purely on native Quickbase formula field methodologies (sorry Dan!).

(-:

David

Photo of David

David

  • 60 Points

Posted 4 years ago

  • 1
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
>Ideally I need the solution to be based purely on native Quickbase formula field methodologies (sorry Dan!).
> ... it would probably be safe to assume there would be no more than 10 instances of "X" in the input field.


That is a strange requirement if you ask me but try this:

List ("Y",
Part([field], 1, "X"),
Part([field], 2, "X"),
Part([field], 3, "X"),
Part([field], 4, "X"),
Part([field], 5, "X"),
Part([field], 6, "X"),
Part([field], 7, "X"),
Part([field], 8, "X"),
Part([field], 9, "X"),
Part([field], 10, "X")
)
Photo of David

David

  • 60 Points
Lol - thanks Dan! Lack of control over the end user environment is why I wanted to avoid non native solutions.

I've not tested your solution yet, but am sure it will work.

Looks so simple! I don't know why but the use of 'List' and 'Part' functions like this really challenges me!

David
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
> ... why I wanted to avoid non native solutions

I never understood why people even use the term "native" in discussing solving problems with QuickBase. You don't have to make an extra purchase of "JavaScript" to use it with QuickBase and there is no other scripting language available with web pages. To me avoiding using JavaScript with QuickBase would be as nonsensical as avoiding using some available feature because its name started with a "J". Would you question that statement:
"I wanted to avoid employing solutions that used a feature with a name that started with a J"?
Photo of David

David

  • 60 Points
Hi Dan,

Personally I don't mind J's.

I'm less keen on K's - in fact I try to avoid them whenever I can.

But maybe I'm prejudiced!

(-:

David

PS The obvious truth is that my comment was born out of naivety - bordering on stupidity, and wanting a solution I could implement instantly despite my relatively basic level of competence. So I greatly appreciate you accommodating my foolish request - and the fact that I suspect you have responded in the middle of the night where you are. (-;
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
> ... I suspect you have responded in the middle of the night where you are

Actually I work through the night preparing the Mas Que Nada conference to be held at a fake location in Brazil.
Photo of David

David

  • 60 Points
Lol - PS Your formula worked a treat (as I knew it would). Job done.