Regular Expression

users
#1

Hi,

I'm in unfamiliar territory, searching a QIF file for a single badly formatted
transaction. I've exported the original moneydance file to a tab limited file with
the thought of searching for a non-numeric character in the transaction amount
column.

The only problem is I can't work out the appropriate Reg. Exp to search for
characters that are not numeric or a "." My brain just refuses to twist into the
required contortions to work out regular expressions!

Can someone give me the formula required in the "Find" box in libre office please?

Keith Bates
New Life Christian Fellowship
Ph 02 67924890

Jesus is the Way,
the Turth and the Life.

#2

[snip]

You want to find everything that's *not* in a particular set, so...

    [^0-9\.]

That's anything that's not zero thru nine or a (literal) dot.

Depending upon the flavour of regexp support, that can also be written
[^[:digit:]\.] or [^\d\.], but the first one I gave works with any
flavour.

HTH

Regards,
Jim

#3

I see this question has already been answered. But an alternative technique may be to import the data into a spreadsheet. Transaction amounts should surely appear as numbers but any rogue value would appear as text. You can spot the difference in a variety of ways:

o Numbers are right-aligned by default, whereas text values are left-aligned.
o View | Value Highlighting (or Ctrl+F8) will colour numbers blue but leave text values black.
o In a separate column, enter a formula such as =IF(ISNUMBER(Xn);"";"Rogue value!") and fill it down the column.
o Create a visible cell style - perhaps with a coloured background. Apply conditional formatting, using "Formula is" and NOT(ISNUMBER(Xn)) - applying your new cell style. Use the Format Paintbrush to apply this down the column.

I trust this helps.

Brian Barker

PS: What's "Turth", please?

#4

Brian Barker

PS: What's "Turth", please?

Brian,

Thanks for your suggestion. I went with the Reg. Exp method but still unable to
find the "rogue transaction" that throws the error.

I could say that "Turth" is a kind of post-modern rejection of truth, but that would
be suggesting that Jesus is not quite the truth. :slight_smile:

I've now fixed the typo as you can see,

Keith Bates
New Life Christian Fellowship
Ph 02 67924890

Jesus is the Way,
the Truth and the Life.

#5

Thanks Jim.
That worked, although it didn't solve my problem with the QIF file

Keith

> Hi,
>
> I'm in unfamiliar territory, searching a QIF file for a single badly
> formatted transaction. I've exported the original moneydance file to
> a tab limited file with the thought of searching for a non-numeric
> character in the transaction amount column.
>
> The only problem is I can't work out the appropriate Reg. Exp to
> search for characters that are not numeric or a "."

[snip]

You want to find everything that's *not* in a particular set, so...

    [^0-9\.]

That's anything that's not zero thru nine or a (literal) dot.

Depending upon the flavour of regexp support, that can also be written
[^[:digit:]\.] or [^\d\.], but the first one I gave works with any
flavour.

HTH

Regards,
Jim

Keith Bates
New Life Christian Fellowship
Ph 02 67924890

Jesus is the Way,
the Truth and the Life.