How to split the invoice amount

Most of the time, the financial department should make the amount according... The format of thousand, hundred, ten, Yuan, angle, and score is split so that the printing template can be printed in the following picture format.

So how do we split the values of a numeric field one by one?

1. Design ideas

1. Add a numeric component to the form to enter raw data.

2. Add the required number of digits in sequence after this value.

3. Write different functions for each numeric component.

4. More than one million values will be split into 9999999

2. Function writing

• Million:

The following code can be directly copied to the formula editor,Note replace the input amount Field.

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,1000000),LT(​录入金额​,10000000)),LEFT(​录入金额​,1),0))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":53,"sticky":null},"to":{"line":0,"ch":59,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":76,"sticky":null},"to":{"line":0,"ch":82,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}

• 100,000 :

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,100000),LE(​录入金额​,10000)),RIGHT(LEFT(​录入金额​,2),1),INT((​录入金额​-(​百万​*1000000))/100000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":52,"sticky":null},"to":{"line":0,"ch":58,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":78,"sticky":null},"to":{"line":0,"ch":84,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":96,"sticky":null},"to":{"line":0,"ch":102,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":104,"sticky":null},"to":{"line":0,"ch":108,"sticky":null},"value":"numberField_ksidlu5w","invalid":false}],"isCmData":true}

• Ten thousand:

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,10000),LE(​录入金额​,1000)),RIGHT(LEFT(​录入金额​,3),1),INT((​录入金额​-(​百万​*1000000)-(​十万​*100000))/10000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":51,"sticky":null},"to":{"line":0,"ch":57,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":76,"sticky":null},"to":{"line":0,"ch":82,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":94,"sticky":null},"to":{"line":0,"ch":100,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":102,"sticky":null},"to":{"line":0,"ch":106,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":117,"sticky":null},"to":{"line":0,"ch":121,"sticky":null},"value":"numberField_ksidlu5y","invalid":false}],"isCmData":true}

• Thousand:

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,1000),LE(​录入金额​,100)),RIGHT(LEFT(​录入金额​,4),1),INT((​录入金额​-(​百万​*1000000)-(​十万​*100000)-(​万​*10000))/1000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":50,"sticky":null},"to":{"line":0,"ch":56,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":74,"sticky":null},"to":{"line":0,"ch":80,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":92,"sticky":null},"to":{"line":0,"ch":98,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":100,"sticky":null},"to":{"line":0,"ch":104,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":115,"sticky":null},"to":{"line":0,"ch":119,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":129,"sticky":null},"to":{"line":0,"ch":132,"sticky":null},"value":"numberField_ksidlu5z","invalid":false}],"isCmData":true}

• Hundred:

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,100),LE(​录入金额​,10)),RIGHT(LEFT(​录入金额​,5),1),INT((​录入金额​-(​百万​*1000000)-(​十万​*100000)-(​万​*10000)-(​ 千​*1000))/100)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":49,"sticky":null},"to":{"line":0,"ch":55,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":72,"sticky":null},"to":{"line":0,"ch":78,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":90,"sticky":null},"to":{"line":0,"ch":96,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":98,"sticky":null},"to":{"line":0,"ch":102,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":113,"sticky":null},"to":{"line":0,"ch":117,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":127,"sticky":null},"to":{"line":0,"ch":130,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":139,"sticky":null},"to":{"line":0,"ch":143,"sticky":null},"value":"numberField_ksidlu5x","invalid":false}],"isCmData":true}

• Ten:

{"text":"IF(GT(​录入金额​,9999999),9,IF(AND(GE(​录入金额​,10),LE(​录入金额​,1)),RIGHT(LEFT(​录入金额​,6),1),INT((​录入金额​-(​百万​*1000000)-(​十万​*100000)-(​万​*10000)-(​ 千​*1000)-(​百​*100))/10)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":48,"sticky":null},"to":{"line":0,"ch":54,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":70,"sticky":null},"to":{"line":0,"ch":76,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":88,"sticky":null},"to":{"line":0,"ch":94,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":96,"sticky":null},"to":{"line":0,"ch":100,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":111,"sticky":null},"to":{"line":0,"ch":115,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":125,"sticky":null},"to":{"line":0,"ch":128,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":137,"sticky":null},"to":{"line":0,"ch":141,"sticky":null},"value":"numberField_ksidlu5x","invalid":false},{"from":{"line":0,"ch":149,"sticky":null},"to":{"line":0,"ch":152,"sticky":null},"value":"numberField_ksidlu60","invalid":false}],"isCmData":true}

• Yuan:

{"text":"IF(GT(​录入金额​,9999999),9,RIGHT(INT(​录入金额​),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}

• Angle:

{"text":"IF(GT(​录入金额​,9999999),9,LEFT(ARRAYGET(SPLIT(TEXT(​录入金额​),\".\"),2),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":49,"sticky":null},"to":{"line":0,"ch":55,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}

• Points:

{"text":"IF(GT(​录入金额​,9999999),9,RIGHT(LEFT(ARRAYGET(SPLIT(TEXT(​录入金额​),\".\"),2),2),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":55,"sticky":null},"to":{"line":0,"ch":61,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}

3. Effect demonstration

Click the form to view the split effect:Click View

YIDA in order to better optimize the content and quality of YIDA user manual, it takes you 3-5 minutes to fill in the document feedback questionnaire. The document feedback questionnaire is submitted anonymously, and the questionnaire information is only used for YIDA document experience feedback collection. Thank you for your support for YIDA!

Click here to fill in the questionnaire

-------------------- Get the latest information YIDA, welcome to follow US--------------------