Excel Formula to Insert Spaces Before Capital Letters and Numbers-CodePudding

In column A, starting with A1, I have a set of database column names which are Pascale case and without spaces. I'd like to use an Excel formula in column B to insert spaces before each Capital letter or number. Ideally any consecutive capital letters or numbers would remain together. I've done this in the past with C#, but on this project, I can't even use VBA macros. Example output:

Can this, or something close, be achieved using only formulas?

CodePudding user response：

This is pretty hard, but with ms365 doable with the give sample data:

Formula in B1:

=MAP(A1:A10,LAMBDA(v,TRIM(REDUCE(v,SEQUENCE(LEN(v),,LEN(v),-1),LAMBDA(a,b,LET(x,MAKEARRAY(26,3,LAMBDA(r,c,CHOOSE(c,CHAR(r 64),CHAR(r 96),r-0))),y,MID(a,b,1),z,MID(a,b 1,1),r,BYCOL(x,LAMBDA(c,SUM(EXACT(c,y) EXACT(c,z)))),IF(MAX(r)=1,LEFT(a,b-1)&IF((CONCAT(r)="110")*(EXACT(UPPER(y),y))," "&y,y&" ")&RIGHT(a,LEN(a)-b),a)))))))

Maybe others have shorter solutions...

CodePudding user response：

Just for fun, this uses a single Reduce but I have defined some auxiliary functions Is.Upper, Is.Lower and Is.Digit:

Upper=lambda(c,if(c="",false,and(code(c)>64,code(c)<91)));
Digit=lambda(c,if(c="",false,and(code(c)>47,code(c)<58)));
Lower=lambda(c,if(c="",false,and(code(c)>96,code(c<123))))

=REDUCE(LEFT(A1,1),SEQUENCE(1,LEN(A1)-1,2),LAMBDA(a,c,a&IF(OR(AND(is.Digit(MID(A1,c,1)),NOT(is.Digit(MID(A1,c-1,1)))),AND(is.Upper(MID(A1,c,1)),OR(NOT(is.Upper(MID(A1,c-1,1))),is.Lower(MID(A1,c 1,1)))))," ","")&MID(A1,c,1)))

This is how the main formula looks in the Advanced Formula Environment:

=REDUCE(
    LEFT(A2, 1),
    SEQUENCE(1, LEN(A2) - 1, 2),
    LAMBDA(a, c,
        a &
            IF(
                OR(
                    AND(
                        is.Digit(MID(A2, c, 1)),
                        NOT(is.Digit(MID(A2, c - 1, 1)))
                    ),
                    AND(
                        is.Upper(MID(A2, c, 1)),
                        OR(
                            NOT(is.Upper(MID(A2, c - 1, 1))),
                            is.Lower(MID(A2, c   1, 1))
                        )
                    )
                ),
                " ",
                ""
            ) & MID(A2, c, 1)
    )
)

Note - assumes string length>1.