Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Nov 25, 2021 10:02 am

Hi

We have an excel file that contains numbers stored as text.
to manually update we have to
- open the file
- Select the column that has numbers stored as text.
- click 'Data' and 'Text to Columns'
- Click 'finish'.
and repeat for all rows with the same problem.

Is there a way to do this using the Spire.XLS library?

we have a license for the latest version.

advirtus
 
Posts: 3
Joined: Mon Oct 07, 2019 2:38 pm

Fri Nov 26, 2021 2:41 am

Hello,

Thanks for your inquiry!

Please refer to the following code to split the cell text to columns by special character. If the code does not match your needs, please provide us with your input file and expected output file for reference.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("E:\\testdoc\\exc2.xlsx");

            //get the  colum you want to split
            CellRange DataRange = workbook.Worksheets[0].Columns[7];

            //set the dataformat to "text"
            DataRange.NumberFormat = "@";

            //split data to many colunms
            string[] splitText = null;
            string text = null;
            for (int i = 0; i < DataRange.CellList.Count; i++)
            {
                text = DataRange.CellList[i].DisplayedText;

                if (text != null)
                {
                    //split text by special text
                    splitText = text.Split('0');
                    //write split result to cell, and set its numberformat
                    for (int j = 0; j < splitText.Length; j++)
                    {
                        workbook.Worksheets[0].Range[DataRange.CellList[i].Row, DataRange.CellList[i].Column + j].Text = splitText[j];
                        workbook.Worksheets[0].Range[DataRange.CellList[i].Row, DataRange.CellList[i].Column + j].NumberFormat = "@";
                    }
                }
            }

            workbook.SaveToFile("a.xlsx");


Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Tue Nov 30, 2021 8:22 am

Hello,

Hope you are doing well!

Has the issue been solved now? Could you please give us some feedback at your convenience?

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS