Combine two functions CONCAT and LOWER with Substitute

[areyes]

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.

[Steve]

Please post a screenshot of the expression you tried.

[Steve]

For reference:

help.appsheet.com

CONCATENATE()

Merged text from parts

help.appsheet.com

LOWER()

Convert text to lowercase.

help.appsheet.com

SUBSTITUTE()

Text with replacements

[SkrOYC]

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])
  ),
  " ",
  ""
)

[MultiTech]

@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), “”), “/”, “”), “.”, “_”), “,”, “”), “”, “”), “:”, “”), “;”, “”), “[”, “”), “]”, “”), “(”, “”), “)”, “”), “{”, “”), “}”, “”), “!”, “”), “@”, “”), “#”, “”), “$”, “”), “%”, “”), “^”, “”), “&”, “”), “*”, “”), “+”, “”), “?”, “”), “|”, “”), “<”, “”), “>”, “”)