Calc vs. Excel Find and Replace with Regular Expressions

Calc is the open source version of Excel. It is part of the Open Office suite of applications you can download from OpenOffice.org. I’m working on a Mac. We had an issue in Excel where we needed to separate the numbers from the letters in alphanumeric codes.

That is, the code looked like: 125e
And we needed 125 e.

Unless you want to get into some mindfrying formula making, you can’t do this in Excel for Mac. You can in Calc. Calc supports regular expressions, so you can easilty do a find and replace.

In the Find field, you enter ([:digit:])([:alpha:]) and in the Replace field, you enter $1 $2. The $1 stands for the first item found (the one enclosed by the first set of parentheses). Then a space. Then $2 stands for the second item found.

Find and Replace with Regular Expressions

Using Regular Expressions in Find and Replace

So 125e becomes 125 e.

Open Office provides a list of regular expressions you can use to perform an operation. Really, we wanted to separate the numbers and letters into separate columns, but you need a space or some symbol to reference it. In Calc, that’s under Data, Text to Columns. Here we would select the space as the delimiter.

Data Text to Columns in Calc

Use the space for a delimiter

Even if you are married to Office, you should try having a fling with OpenOffice.org. Lots of times, if I cannot do something in Word or Excel, I find I can do it in Writer or Calc.

See ya soon.

Advertisements

About this entry