// Create a new workbook Workbook workbook = new Workbook(); // TEXTBEFORE Function Example IWorksheet sheetOfTEXTBEFORE = workbook.getActiveSheet(); sheetOfTEXTBEFORE.setName("TEXTBEFORE"); sheetOfTEXTBEFORE.getRange("A1").setValue("Data"); sheetOfTEXTBEFORE.getRange("A1").getFont().setBold(true); sheetOfTEXTBEFORE.getRange("A2").setValue("Little Red Riding Hood's red hood"); sheetOfTEXTBEFORE.getRange("A3").setValue("Little red Riding Hood's red hood"); sheetOfTEXTBEFORE.getRange("A4").setValue("Formulas"); sheetOfTEXTBEFORE.getRange("A4").getFont().setBold(true); sheetOfTEXTBEFORE.getRange("B4").setValue("Results"); sheetOfTEXTBEFORE.getRange("B4").getFont().setBold(true); sheetOfTEXTBEFORE.getRange("B5").setFormula2("=TEXTBEFORE(A2,\"Red\")"); sheetOfTEXTBEFORE.getRange("A5").setFormula("=FORMULATEXT(B5)"); sheetOfTEXTBEFORE.getRange("B6").setFormula2("=TEXTBEFORE(A3,\"red\",2)"); sheetOfTEXTBEFORE.getRange("A6").setFormula("=FORMULATEXT(B6)"); sheetOfTEXTBEFORE.getRange("B7").setFormula2("=TEXTBEFORE(A3,\"red\",-2)"); sheetOfTEXTBEFORE.getRange("A7").setFormula("=FORMULATEXT(B7)"); sheetOfTEXTBEFORE.getRange("B8").setFormula2("=TEXTBEFORE(A3,\"Red\")"); sheetOfTEXTBEFORE.getRange("A8").setFormula("=FORMULATEXT(B8)"); sheetOfTEXTBEFORE.getRange("B9").setFormula2("=TEXTBEFORE(A3,\"Red\",,1)"); sheetOfTEXTBEFORE.getRange("A9").setFormula("=FORMULATEXT(B9)"); sheetOfTEXTBEFORE.getRange("B10").setFormula2("=TEXTBEFORE(A3,\"Riding\")"); sheetOfTEXTBEFORE.getRange("A10").setFormula("=FORMULATEXT(B10)"); sheetOfTEXTBEFORE.getColumns().get(0).autoFit(); sheetOfTEXTBEFORE.getColumns().get(1).autoFit(); // TEXTAFTER Function Example IWorksheet sheetOfTEXTAFTER = workbook.getWorksheets().add(); sheetOfTEXTAFTER.setName("TEXTAFTER"); sheetOfTEXTAFTER.getRange("A1").setValue("Data"); sheetOfTEXTAFTER.getRange("A1").getFont().setBold(true); sheetOfTEXTAFTER.getRange("A2").setValue("Little Red Riding Hood's red hood"); sheetOfTEXTAFTER.getRange("A3").setValue("Little red Riding Hood's red hood"); sheetOfTEXTAFTER.getRange("A4").setValue("Formulas"); sheetOfTEXTAFTER.getRange("A4").getFont().setBold(true); sheetOfTEXTAFTER.getRange("B4").setValue("Results"); sheetOfTEXTAFTER.getRange("B4").getFont().setBold(true); sheetOfTEXTAFTER.getRange("B5").setFormula2("=TEXTAFTER(A2,\"Red\")"); sheetOfTEXTAFTER.getRange("A5").setFormula("=FORMULATEXT(B5)"); sheetOfTEXTAFTER.getRange("B6").setFormula2("=TEXTAFTER(A3,\"red\",2)"); sheetOfTEXTAFTER.getRange("A6").setFormula("=FORMULATEXT(B6)"); sheetOfTEXTAFTER.getRange("B7").setFormula2("=TEXTAFTER(A3,\"red\",-2)"); sheetOfTEXTAFTER.getRange("A7").setFormula("=FORMULATEXT(B7)"); sheetOfTEXTAFTER.getRange("B8").setFormula2("=TEXTAFTER(A3,\"Red\")"); sheetOfTEXTAFTER.getRange("A8").setFormula("=FORMULATEXT(B8)"); sheetOfTEXTAFTER.getRange("B9").setFormula2("=TEXTAFTER(A3,\"Red\",,1)"); sheetOfTEXTAFTER.getRange("A9").setFormula("=FORMULATEXT(B9)"); sheetOfTEXTAFTER.getRange("B10").setFormula2("=TEXTAFTER(A3,\"Riding\")"); sheetOfTEXTAFTER.getRange("A10").setFormula("=FORMULATEXT(B10)"); sheetOfTEXTAFTER.getColumns().get(0).autoFit(); sheetOfTEXTAFTER.getColumns().get(1).autoFit(); // TEXTSPLIT Function Example IWorksheet sheetOfTEXTSPLIT = workbook.getWorksheets().add(); sheetOfTEXTSPLIT.setName("TEXTSPLIT"); sheetOfTEXTSPLIT.getRange("A1").setValue("Data"); sheetOfTEXTSPLIT.getRange("A1").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("A2").setValue("Dakota Lennon Sanchez"); sheetOfTEXTSPLIT.getRange("A3").setValue("To be or not to be"); sheetOfTEXTSPLIT.getRange("A4").setValue("1,2,3;4,5,6"); sheetOfTEXTSPLIT.getRange("A6").setValue("Formulas"); sheetOfTEXTSPLIT.getRange("A6").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("A7").setValue("Results"); sheetOfTEXTSPLIT.getRange("A7").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("B7").setFormula2("=TEXTSPLIT(A2,\" \")"); sheetOfTEXTSPLIT.getRange("B6").setFormula("=FORMULATEXT(B7)"); sheetOfTEXTSPLIT.getRange("A9").setValue("Formulas"); sheetOfTEXTSPLIT.getRange("A9").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("A10").setValue("Results"); sheetOfTEXTSPLIT.getRange("A10").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("B10").setFormula2("=TEXTSPLIT(A3,\" \")"); sheetOfTEXTSPLIT.getRange("B9").setFormula("=FORMULATEXT(B10)"); sheetOfTEXTSPLIT.getRange("A12").setValue("Formulas"); sheetOfTEXTSPLIT.getRange("A12").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("A13").setValue("Results"); sheetOfTEXTSPLIT.getRange("A13").getFont().setBold(true); sheetOfTEXTSPLIT.getRange("B13").setFormula2("=TEXTSPLIT(A4,\",\",\";\")"); sheetOfTEXTSPLIT.getRange("B12").setFormula("=FORMULATEXT(B13)"); sheetOfTEXTSPLIT.getColumns().get(0).autoFit(); sheetOfTEXTSPLIT.getColumns().get(1).autoFit(); // Save to an excel file workbook.save("NewTextManipulationFunctions.xlsx");
// Create a new workbook var workbook = Workbook() // TEXTBEFORE Function Example val sheetOfTEXTBEFORE = workbook.activeSheet sheetOfTEXTBEFORE.name = "TEXTBEFORE" sheetOfTEXTBEFORE.getRange("A1").value = "Data" sheetOfTEXTBEFORE.getRange("A1").font.bold = true sheetOfTEXTBEFORE.getRange("A2").value = "Little Red Riding Hood's red hood" sheetOfTEXTBEFORE.getRange("A3").value = "Little red Riding Hood's red hood" sheetOfTEXTBEFORE.getRange("A4").value = "Formulas" sheetOfTEXTBEFORE.getRange("A4").font.bold = true sheetOfTEXTBEFORE.getRange("B4").value = "Results" sheetOfTEXTBEFORE.getRange("B4").font.bold = true sheetOfTEXTBEFORE.getRange("B5").formula2 = "=TEXTBEFORE(A2,\"Red\")" sheetOfTEXTBEFORE.getRange("A5").formula = "=FORMULATEXT(B5)" sheetOfTEXTBEFORE.getRange("B6").formula2 = "=TEXTBEFORE(A3,\"red\",2)" sheetOfTEXTBEFORE.getRange("A6").formula = "=FORMULATEXT(B6)" sheetOfTEXTBEFORE.getRange("B7").formula2 = "=TEXTBEFORE(A3,\"red\",-2)" sheetOfTEXTBEFORE.getRange("A7").formula = "=FORMULATEXT(B7)" sheetOfTEXTBEFORE.getRange("B8").formula2 = "=TEXTBEFORE(A3,\"Red\")" sheetOfTEXTBEFORE.getRange("A8").formula = "=FORMULATEXT(B8)" sheetOfTEXTBEFORE.getRange("B9").formula2 = "=TEXTBEFORE(A3,\"Red\",,1)" sheetOfTEXTBEFORE.getRange("A9").formula = "=FORMULATEXT(B9)" sheetOfTEXTBEFORE.getRange("B10").formula2 = "=TEXTBEFORE(A3,\"Riding\")" sheetOfTEXTBEFORE.getRange("A10").formula = "=FORMULATEXT(B10)" sheetOfTEXTBEFORE.columns[0].autoFit() sheetOfTEXTBEFORE.columns[1].autoFit() // TEXTAFTER Function Example val sheetOfTEXTAFTER = workbook.worksheets.add() sheetOfTEXTAFTER.name = "TEXTAFTER" sheetOfTEXTAFTER.getRange("A1").value = "Data" sheetOfTEXTAFTER.getRange("A1").font.bold = true sheetOfTEXTAFTER.getRange("A2").value = "Little Red Riding Hood's red hood" sheetOfTEXTAFTER.getRange("A3").value = "Little red Riding Hood's red hood" sheetOfTEXTAFTER.getRange("A4").value = "Formulas" sheetOfTEXTAFTER.getRange("A4").font.bold = true sheetOfTEXTAFTER.getRange("B4").value = "Results" sheetOfTEXTAFTER.getRange("B4").font.bold = true sheetOfTEXTAFTER.getRange("B5").formula2 = "=TEXTAFTER(A2,\"Red\")" sheetOfTEXTAFTER.getRange("A5").formula = "=FORMULATEXT(B5)" sheetOfTEXTAFTER.getRange("B6").formula2 = "=TEXTAFTER(A3,\"red\",2)" sheetOfTEXTAFTER.getRange("A6").formula = "=FORMULATEXT(B6)" sheetOfTEXTAFTER.getRange("B7").formula2 = "=TEXTAFTER(A3,\"red\",-2)" sheetOfTEXTAFTER.getRange("A7").formula = "=FORMULATEXT(B7)" sheetOfTEXTAFTER.getRange("B8").formula2 = "=TEXTAFTER(A3,\"Red\")" sheetOfTEXTAFTER.getRange("A8").formula = "=FORMULATEXT(B8)" sheetOfTEXTAFTER.getRange("B9").formula2 = "=TEXTAFTER(A3,\"Red\",,1)" sheetOfTEXTAFTER.getRange("A9").formula = "=FORMULATEXT(B9)" sheetOfTEXTAFTER.getRange("B10").formula2 = "=TEXTAFTER(A3,\"Riding\")" sheetOfTEXTAFTER.getRange("A10").formula = "=FORMULATEXT(B10)" sheetOfTEXTAFTER.columns[0].autoFit() sheetOfTEXTAFTER.columns[1].autoFit() // TEXTSPLIT Function Example val sheetOfTEXTSPLIT = workbook.worksheets.add() sheetOfTEXTSPLIT.name = "TEXTSPLIT" sheetOfTEXTSPLIT.getRange("A1").value = "Data" sheetOfTEXTSPLIT.getRange("A1").font.bold = true sheetOfTEXTSPLIT.getRange("A2").value = "Dakota Lennon Sanchez" sheetOfTEXTSPLIT.getRange("A3").value = "To be or not to be" sheetOfTEXTSPLIT.getRange("A4").value = "1,2,3;4,5,6" sheetOfTEXTSPLIT.getRange("A6").value = "Formulas" sheetOfTEXTSPLIT.getRange("A6").font.bold = true sheetOfTEXTSPLIT.getRange("A7").value = "Results" sheetOfTEXTSPLIT.getRange("A7").font.bold = true sheetOfTEXTSPLIT.getRange("B7").formula2 = "=TEXTSPLIT(A2,\" \")" sheetOfTEXTSPLIT.getRange("B6").formula = "=FORMULATEXT(B7)" sheetOfTEXTSPLIT.getRange("A9").value = "Formulas" sheetOfTEXTSPLIT.getRange("A9").font.bold = true sheetOfTEXTSPLIT.getRange("A10").value = "Results" sheetOfTEXTSPLIT.getRange("A10").font.bold = true sheetOfTEXTSPLIT.getRange("B10").formula2 = "=TEXTSPLIT(A3,\" \")" sheetOfTEXTSPLIT.getRange("B9").formula = "=FORMULATEXT(B10)" sheetOfTEXTSPLIT.getRange("A12").value = "Formulas" sheetOfTEXTSPLIT.getRange("A12").font.bold = true sheetOfTEXTSPLIT.getRange("A13").value = "Results" sheetOfTEXTSPLIT.getRange("A13").font.bold = true sheetOfTEXTSPLIT.getRange("B13").formula2 = "=TEXTSPLIT(A4,\",\",\";\")" sheetOfTEXTSPLIT.getRange("B12").formula = "=FORMULATEXT(B13)" sheetOfTEXTSPLIT.columns[0].autoFit() sheetOfTEXTSPLIT.columns[1].autoFit() // Save to an excel file workbook.save("NewTextManipulationFunctions.xlsx")