Combine two functions CONCAT and LOWER with Substitute

AppSheet AppSheet Q&A
1

I would like to Concatenate [CustomerID] and [SiteID] in my APP and Lower the letters and user substitute to put everything together without spaces.
The final result would be like: customernorthoffice.

I’ve tried to combine them all but no luck. Appreciate your help.

2

Please post a screenshot of the expression you tried.

1 Like
3

For reference:

2 Likes
4

One step at a time I guess:

SUBSTITUTE(
  LOWER(
    CONCATENATE(
      [CustomerID],
      [SiteID]
    )
  ),
  " ",
  ""
)

OR

LOWER(
  SUBSTITUTE(
    CONCATENATE(
      [CustomerID],
      [SiteID]
    ),
    " ",
    ""
  )
)

OR

SUBSTITUTE(
  CONCATENATE(
    LOWER([CustomerID]),
    LOWER([SiteID])
  ),
  " ",
  ""
)
4 Likes
5

@areyes You might like the formula I use to generate common sense keys:

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(> > TRIM([ENTER_YOUR_COLUMN_HERE]),> > " , ", “"), ", ", "”), " “, “"), “-”, "”), left(”’ “, 1), “”), left(’” ', 1), “”), “/”, “”), “.”, “”), “,”, “”), “”, “”), “:”, “”), “;”, “”), “[”, “”), “]”, “”), “(”, “”), “)”, “”), “{”, “”), “}”, “”), “!”, “”), “@”, “”), “#”, “”), “$”, “”), “%”, “”), “^”, “”), “&”, “”), “*”, “”), “+”, “”), “?”, “”), “|”, “”), “<”, “”), “>”, “”)## As you can see it’s a bunch of nested substitutes (to take out all special characters, converting others (like a space) into an underscore “”).

  • I use this to take data entered by a user (like a company name) and then use THAT as the ID, but I clean things up a bit first.

You can easily convert this to accept two values

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(> > Lower(TRIM(Concatenate([CustomerID], “”, [SiteID]))),> > " , ", “"), ", ", "”), " “, “"), “-”, "”), left(”’ “, 1), “”), left(’” ', 1), “”), “/”, “”), “.”, “”), “,”, “”), “”, “”), “:”, “”), “;”, “”), “[”, “”), “]”, “”), “(”, “”), “)”, “”), “{”, “”), “}”, “”), “!”, “”), “@”, “”), “#”, “”), “$”, “”), “%”, “”), “^”, “”), “&”, “”), “*”, “”), “+”, “”), “?”, “”), “|”, “”), “<”, “”), “>”, “”)

1 Like