11

Trying to create a formula to turn a string of words separated by spaces into camelcase

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Ryan Schumacher
  • 1,816
  • 2
  • 21
  • 33

4 Answers4

26

Much smaller version: 

=SUBSTITUTE(PROPER(TRIM(A1))," ","")

We just use PROPER to upper case and TRIM and SUBSTITUTE to remove spaces.

If we want lowerCamelCase,

By just REPLACEing the first character with lower case, We have: 

=REPLACE(SUBSTITUTE(PROPER(TRIM(A1))," ",),1,1,LEFT(LOWER(TRIM(A1))))

Using REGEX:

=REGEXREPLACE(REGEXREPLACE(PROPER(A1),"\s*",),"^(\w)",LEFT(LOWER(TRIM(A1))))

=LOWER(LEFT(TRIM(A1)))&REGEXREPLACE(PROPER(TRIM(A1)),"(\w|\s)(\w*)","$2")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • My understanding is that this will look more like `SomeWordsHere` instead of `someWordsHere`. Correct me if I’m wrong. – Ryan Schumacher Sep 30 '17 at 20:43
  • @Ryan,Yes. I thought that was the original Ⓠ. If you want lower camel casing, it's not that hard from here: `=REPLACE(SUBSTITUTE(PROPER(TRIM(A3))," ",""), 1,1,LOWER(LEFT(TRIM(A3))))` This will give `someWordsHere` lowerCamelCase – TheMaster Oct 01 '17 at 04:02
  • 1
    Without the `TRIM` failsafes this formula would look much smaller. – TheMaster Oct 01 '17 at 04:28
5

This should work:

=JOIN("",ArrayFormula(UPPER(LEFT(SPLIT(A3," ")))&LOWER(MID(SPLIT(A3," "),2,500))))

or to be more precise:

=JOIN("",ArrayFormula(UPPER(LEFT(SPLIT(A3," ")))&LOWER(REGEXEXTRACT(SPLIT(A3," "),".(.*)"))))
Mojtaba
  • 6,012
  • 4
  • 26
  • 40
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
4

To do this the following formula works (where A3 is the cell)

tl;dr: 

=IF(IFERROR(FIND(" ",A3)), CONCAT(SUBSTITUTE(LEFT(LOWER(A3), FIND(" ", A3)), " ", ""), SUBSTITUTE(PROPER(SUBSTITUTE(A3, LEFT(A3, FIND(" ", A3)), "")), " ", "")), LOWER(A3))

Annotated:

=IF(                               // if a single word
    IFERROR(                       // test if NOT an error
        FIND(                      // looking for a space
            " ",
            A3
        )
    ),
    CONCAT(                        // concat the first word with the rest
        SUBSTITUTE(                // remove the space
            LEFT(                  // left of the find
                LOWER(             // lowercase the string
                    A3
                ),
                FIND(              // find the space in the string
                    " ",
                    A3
                )
            ),
            " ",
            ""
        ),
        SUBSTITUTE(                // remove spaces
            PROPER(                // convert string to capitals
                SUBSTITUTE(        // remove first word
                    A3,
                    LEFT(          // left of the find
                        A3,
                        FIND(      // find first space
                            " ",
                            A3
                        )
                    ),
                    ""
                )
            ),
            " ",
            ""
        )
    ),
    LOWER(                      // lowercase rest of the word
        A3
    )
)
Ryan Schumacher
  • 1,816
  • 2
  • 21
  • 33
0

Taking cue from all answers, sharing what I did as no one has mentioned it in the following exact way (transformation e.g. created_at => createdAt)

=REPLACE(SUBSTITUTE(PROPER(A5), "_", ""), 1, 1, LOWER(LEFT(A5, 1)))

The above formula is

  • Easy => uses simple to understand popular functions
  • Correct => works on empty cells too
  • Efficient => parses the "whole" string only ONCE

Most of the work is done using PROPER function except the last part to replace first character with its lowercase version

comiventor
  • 3,922
  • 5
  • 50
  • 77