VBA routines for splitting and joining text

As mentioned in the previous post, I have written two short VBA routines to aid the process of splitting a column of text strings into separate columns, using either a space or any other chosen character as the delimiter.  These routines have been added to the Text-in2 spreadsheet, along with a new JoinText function to reverse the process.  The new file can be downloaded (including full open source code) from:


For an example of the use of the new routines see the Txt2Col sheet:


Text (including text from pdf files) can be copied and pasted anywhere.  Select all the rows and as many columns as you want to split, then press Alt-F8, select Text2TextCols, and click Run:


The text in the first column is split into the selected columns in text format, so that the original number formats are retained:


The ResetTxt2Cols macro is for use when the Excel Text to Columns wizard has been used, and you want to paste text copied from external files into a single column.  To run press Alt-F8, select ResetTxt2Cols, and click run.

Split text (or any other text in a continuous column or row) can be combined with the JoinText user defined function (UDF) as shown below:


JoinText has two optional arguments:

  • Separate defines the separator to add between cell contents (default a single space).
  • IgnoreBlank ignores blank cells if set to true.

Excel 2016 now has two new built in functions providing similar functionality, Concat() and TextJoin().  The JoinText UDF still has a couple of advantages however:

  • It will work in any version of Excel that supports VBA.
  •  The Separate and IgnoreBlank arguments are optional, simplifying use when the default values are to be used.
Posted in Excel, UDFs, VBA | Tagged , , , , , , , | Leave a comment

The error made in 20% of papers on genes …

… and how to avoid it.

According to a recent scientific paper “Gene name errors are widespread in the scientific literature” (authors: Mark Ziemann, Yotam Eren1, and Assam El-Osta).  The paper says that “approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions”, and that the errors are caused by Excel converting certain gene names into dates, and others into numbers in scientific notation.  A search finds this is nothing new.  A 2004 paper states “Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics”.  The more recent paper has an interesting graph of the frequency of these errors over time:

It seems that the errors were at a low level after the 2004 paper, but have since risen substantially.

Errors of this type are of course not limited to gene names.  Problems with dates being interpreted differently in different regions are widespread, and in the engineering context fractions may be converted to dates, or left as a fraction in text format.

Although  widespread, these problems are reasonably easy to avoid.  This post will look at the built in Excel methods, and the next will present some VBA solutions.  As an example, we will import a table from a pdf file.  The table is copied to the clipboard:txt2cols1-0

When pasted in Excel all the text goes in one column:


The text can be split into columns with the Text to Columns Wizard, under the Data tab, which has three steps.  First select the “delimited” option:


For delimiters select “space” and “treat consecutive delimiters as one”:


Finally select “Text” as the data format for all columns.  To do this in one operation scroll to the right of the data preview, hold down the Ctrl key, and click on the right hand column:


The text is split into columns with text format, so the fractions display as formatted in the original document:


If the same clipboard data is now pasted into another range, Excel remembers the text to columns with space delimiters settings, but not the text format setting, so the integers are pasted as numbers, but the fractions are pasted as either dates or text, depending on whether the fraction can be interpreted as a valid date or not:


To paste the data as text all the cells in the paste range must be formatted as text before pasting.  All the fraction cells will then be pasted in their original format:


The settings selected in the Text to Columns Wizard will remain in place so long as the spreadsheet is open, even if another workbook is opened.  Options for resetting, so that pasted text will go into a single column again are:

  1. Save and re-open the spreadsheet
  2. Or select a single cell containing text, and go through the Text to Columns process, selecting “delimited” but deselecting all delimiters.
Posted in Excel | Tagged , , , , | Leave a comment

A long history of civilisation …

… and climate change:



Posted in Climate, Newton | Tagged , , | 2 Comments

Non-linear Frame Analysis – Scipy solvers

The frame analysis spreadsheet presented in the previous post has been updated to use the solvers included in the Scipy package.  There is now an option to use either the Cholesky solver, or an iterative sparse solver.  The main advantages of this change are:

  1. Cholesky factorisation is the same method as used in the original Fortran code, but the Scipy solver makes better use of multi-core processors, and is significantly faster for large frames.
  2. For very large frames the iterative sparse solver provides much better performance, and will work with much larger frames without hitting memory limits.

In addition to linking to the Scipy functions it was necessary to modify the format of the stiffness matrix.  The Cholesky function uses a lower triangle banded format, and the sparse solver uses a COO sparse format (see the Scipy manual for details).  Using Python to generate these matrices was found to be very slow, so short Fortran routines were added to the main module.

As before, the new spreadsheet and related files, including full open source code, may be downloaded from:


See the previous post for details of software required, and installation details.

In addition to the 3D frame with 1476 beams used in the previous post, two larger frames were analysed, with 7065 beams:


and 14130 beams:nl-frame2-2

For the frame used in the previous post the new solvers made little difference to performance, but with the first of the larger frames the time for the first iteration using the Fortran solver increased from about half a second to between 15 and 60 seconds, depending on the numbering system.  Using the Scipy Cholesky solver, this was reduced to about 1 second for the first iteration (including the matrix factorisation stage), and about 0.5 seconds for each subsequent iterations, allowing iterative solution of 8 non-linear load stages in abut 90 seconds:


As before, beam shears and moments were compared with results from the Strand7 package, showing good agreement:


With the largest frame the first iteration using the Cholesky solver took about 60 seconds, but the sparse solver took only 3 seconds:nl-frame2-5

Posted in Arrays, Beam Bending, Excel, Finite Element Analysis, Fortran, Frame Analysis, Link to dll, Link to Python, Newton, NumPy and SciPy, Strand7, UDFs, VBA | Tagged , , , , , , , , , , , , , | Leave a comment

Non-linear Frame Analysis

Another example using Fortran code published in Programming the Finite Element Method (5th ed. John Wiley & Sons, I.M. Smith, D.V. Griffiths and L. Margetts (2014)), this post provides a spreadsheet based frame analysis program including non-linear bending behaviour and 2D or 3D analysis, linking to Fortran solver functions, via Python and xlwings.  Note that at present the spreadsheet has only rudimentary functionality for post-processing and plotting of results, and only provides input for loads at nodes, so for linear analysis the spreadsheets Frame4 and 3DFrame provide better functionality.

The new spreadsheet and related files, including full open source code, may be downloaded from:


To run the spreadsheet, in addition to Excel, the requirements are:

  • Python, including Numpy, Scipy and Ctypes
  • Xlwings
  • The provided compiled Fortran files, Main.dll and Geom.dll may be installed anywhere on the system path, or in the same directory as the spreadsheet and Python files.

The simplest way to install the required Python modules is to install Anaconda Python, which includes xlwings.  The program has been tested with Python 2.7.  It should work with Python 3, but if not, please let me know.

The program is based on Program P45 from Edition 5 of Programming the Finite element Method.  The beam elements are treated as elastic – perfectly plastic, and specified nodal loads may be factored in any number of load increments.  The download file includes data for the 3D frame analysis shown below:


Data is input on the spreadsheet, with any number of material types, nodes and beam elements (up to the 1 million+ rows provided by Excel):


Forces and moments are applied at nodes only in the current version.  Specified loads are factored by any number of load increments:


Input for 2D analyses is in a similar format, with data restricted to the available freedoms (X and Y directions, and moments about the Z axis):


The analysis is run by clicking a button on the “Results” sheet.  Full lists of node deflections and beam end actions are copied to the “Deflect” and “StressRes” sheets respectively.  At present the “Results” sheet only provides a brief summary of each load increment, and plots of deflections for one node, and forces and moments for one beam, with up to 8 load increments.  This will be extended in later versions.


The example output also plots X deflection and Z-axis moment results from the finite element analysis program Strand7, using the same elastic-plastic beam properties.  It can be seen that there is good agreement between the two, although not exact due to different methods of modelling the non-linear behaviour.


Strand7 provides a facility to re-number nodes, to improve the efficiency of the matrix solution process.  Three different node numbering sequences have been copied to the spreadsheet, and it can be seen below (row 7) that the solution times for this medium sized frame are almost unchanged.  For a larger frame (2730 Nodes and 7065 beams) the “tree” node sequence was about four times faster than the other two options.


Posted in Beam Bending, Excel, Finite Element Analysis, Fortran, Frame Analysis, Link to dll, Link to Python, Newton, NumPy and SciPy, Strand7, UDFs, VBA | Tagged , , , , , , , , , , | 3 Comments

Darrell Scott & Danny Thompson

Playing at HebCelt 2013 in Stornoway.

Don’t forget the headphones:

… and a slightly older recording featuring Danny Thompson with Pentangle (who were Jacqui Mcshee, Bert Jansch, John Renbourn and Terry Cox)

Posted in Bach | Tagged , , , | Leave a comment

ConbeamU 4.10

The ConbeamU spreadsheet has been updated to Version 4.10, to fix a problem with incorrect default parameters being used if the support stiffness columns had empty cells.  The revised spreadsheet (including open-source code) can be downloaded from:


Applications in the spreadsheet include:

Conbeam and ConbeamU; continuous beam analysis with any number of supports and beam segments.  Supports may have specified translation or rotation stiffness or specified displacements.  ConbeamU (and other functions ending in U) are unit aware, allowing input and output in a wide variety of different units:

There are also similar functions for single spans (SSSpanU) and cantilevers (CantileverU).

MovLoadU does moving load analysis on a continuous beam, with vehicles with any number of axles:


FEAU and REAU find fixed end moments or restrained end moments for a single span with fixed ends or spring restrained ends.  The beam may have any number of segments with different section properties.


Any number of loads may be applied, which may be point forces or moments, of uniform or trapezoidal distributed loads:


The BeamAct3D function returns beam actions and deflections along the beam, for a beam with any number of segments and specified end conditions and applied loads.


The React3D function returns fixed or restrained actions for a beam with any number of segments, subject to 3D loading:


Download the spreadsheet for more details of each function, and information on using array functions.

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, Strand7, UDFs, VBA | Tagged , , , , , , , , | 8 Comments