Sharepoint Calculated Formula

0
votes

Good day,

I'm having troubles with Sharepoint Column Calculated value. This column is used to create computer code depending on company, office, name first letter and surname first 5 letters, the problem is that Lithuanian names has lithuanian letters. So I need this formula to replace name and surname letters Ą to A, Č to C, Ę to E, Ė to E, Į to I, Š to S, Ų to U, Ū to U, Ž to Z . For example depending on my name, my computer code is GRIADMAMAKŠE, but I need it to generate GRIADMAMAKSE. Thanks in advance! 

=UPPER(IF(Company="Grigeo Grigiškės";"GRI";"")&IF(Company="Grigeo Baltwood";"BWO";"")&IF(Company="Grigeo Klaipėdos Kartonas";"DAT";"")&IF(Company="Grigeo Recycling";"REC";"")&IF(Office="Office";"ADM";"")&IF(Office="Factory";"GAM";"")&(LEFT([Name];1)&LEFT([Surname];5)))
1
sharepointexcel-formulaformula

1 Answers

0
votes

Since there is no REPLACE function in SharePoint, the only way is to do something like this

=IF(
    ISNUMBER(
        FIND("à",[YourColumn])
    );
    REPLACE([YourColumn],FIND("à",[YourColumn]),1,"a"),
    [YourColumn]
)

At first you check if the string contains the character to avoid #VALUE! exception, then you replace it.

The formula above works for a single letter, you need to nest the functions to replace all characters.

It's a little tricky but it's possible. Good luck ;)

Note: if you aren't using english as regional settings, you have to replace commas with ";" in the formula.