// 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");