Help

Re: SUBSTITUTE Formula - Not removing apostrophe

Solved
Jump to Solution
1125 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Sparkn_AI
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey there, looking to create a simple slug and for some reason the apostrophe isn’t being removed.

formula

Example Title: Zoom Fatigue is real, so let’s deal with it.

So make lowercase, remove apostrophes remove commas, remove full stops and replace spaces with dashes (etc) is what I’m after.

LOWER(
SUBSTITUTE(
	SUBSTITUTE(
		SUBSTITUTE(
			SUBSTITUTE(
				{Name}, "'",""
				),
				",", ""
			),
			".", ""
		),
		" ", "-"
	)
)

I cannot fathom why the apostrophe is not being removed. I’m missing something easy… ideas?

1 Solution

Accepted Solutions
Martin_Kopischk
7 - App Architect
7 - App Architect

Hard to tell because of the resolution of your screenshot, but might that apostrophe be a typographically correct one (), not the ASCII single quote (') your formula is replacing? You can cover that case and get rid of other nested SUBSTITUTEs by using REGEX_REPLACE. For instance, this:

LOWER(
  REGEX_REPLACE(
    REGEX_REPLACE(Name, "['’´,.]+", ""),
    "\s+", "-"
  )
)

will substitute a hyphen for whitespace and remove dots, commas and common apostrophe variants.

See Solution in Thread

2 Replies 2
Martin_Kopischk
7 - App Architect
7 - App Architect

Hard to tell because of the resolution of your screenshot, but might that apostrophe be a typographically correct one (), not the ASCII single quote (') your formula is replacing? You can cover that case and get rid of other nested SUBSTITUTEs by using REGEX_REPLACE. For instance, this:

LOWER(
  REGEX_REPLACE(
    REGEX_REPLACE(Name, "['’´,.]+", ""),
    "\s+", "-"
  )
)

will substitute a hyphen for whitespace and remove dots, commas and common apostrophe variants.

My dude! Thank you. I now know about REGEX-REPLACE. :pray: