Forum Discussion

BradElmore's avatar
BradElmore
Qrew Assistant Captain
9 years ago

Remove blank spaces and make lower case

I would like to create a text formula that removes blank spaces and changes text to lower case. For example: 
[Field A] = New York City
[new formula field] = newyorkcity

thanks
  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    9 years ago

    This formula text field removes up to four embedded spaces. You can understand the logic and add more Part() function terms if your strings have more spaces embedded.

    Lower(
    Left([Field to fix]," ")&Part([Field to fix],2," ")&Part([Field to fix],3," ")&Part([Field to fix],4," ")
    )

  • BradElmore's avatar
    BradElmore
    Qrew Assistant Captain
    9 years ago
    Ended up using a solution the Mark suggested awhile back.....

    If I used:  Lower(Part([City], 1," ") & Part([City], 2," ") then New York City would become: newyork (no city)

    So I had to add a few more "Parts"
    Lower(Part([City], 1," ") & Part([City], 2," ") & Part([City], 3," ") & Part([City], 4," "))
  • BrandonLopez1's avatar
    BrandonLopez1
    Qrew Member
    2 years ago

    I was looking for something similar and figured I'd post what worked for me. I used SearchAndReplace combined with Upper in my situation but you can use Lower.

    SearchAndReplace(Lower([Field A]), " ", "")

    Doing it this way does not require multiple Parts.

    Here is the function reference I used.



    ------------------------------
    Brandon Lopez
    ------------------------------