Extracting a value from a string

[richard_chilvers]

My table has a field which contains notes with a value in. For example:

"The cost of the purchase is £234.56 payable by cheque"

I wish to extract the numeric part as a value.

I have used TextBetween(NOTE_TEXT,'£',' ') AS Value

which works well, except sometimes there is no space after the value, eg.

"The cost of the purchase is £234.56per person payable by cheque".

Is there anyway I can identify the TextBetween '£' and a non-numeric character?

Thanks

[kangaroomac]

Hiya,

Have you tried the keepchar function?

KeepChar(NOTE_TEXT)

[its_anandrjs]

Use Keepchar fuction like

=KeepChar(Text,'0123456789')

or

=Keepchar('The cost of the purchase is £234.56 payable by cheque','0123456789')

Hope this helps

[its_anandrjs]

For your field

Keepchar(NOTE_TEXT,'0123456789') AS Value

Hope this helps

[kangaroomac]

And if you want to knit pick, add the full stop decimal indicator (.) as well - else the cost of the purchase is going to be a bit more than expected.

i.e.

Keepchar(NOTE_TEXT,'0123456789.') AS Value

[richard_chilvers]

Hi

Thanks for a good suggestion.

Keepchar sounds good, but it won't work in my case; for example, the note might say:

"The cost of the purchase is £234.56 payable by cheque which covers 15 people".

So I need to only capture the value after the £ sign.

The challenge we all face with unstructured data

[kangaroomac]

Would this help?

Subfield(Keepchar(NOTE_TEXT,'0123456789.') , '$') AS Value

or the three parameter version:

Subfield(Keepchar(NOTE_TEXT,'0123456789.') , '$', 1) AS Value

(Substitute the dollar for a pound sign)

[its_anandrjs]

If you want to keep decimal value also then write

Keepchar(NOTE_TEXT,'0123456789.') AS Value

[tresesco]

Try like this, using FindOneOf() and Index()

=Mid('The cost of the purchase is £234.56per person payable by cheque',Index('The cost of the purchase is £234.56per person payable by cheque', '£')+1,FindOneOf(Mid('The cost of the purchase is £234.56per person payable by cheque',Index('The cost of the purchase is £234.56per person payable by cheque', '£')),'abcdefghijklmnopqrstuvwxyz')-2)

Generic:

=Mid(YourTextField,Index(YourTextField, '£')+1,FindOneOf(Mid(YourTextField,Index(YourTextField, '£')),'abcdefghijklmnopqrstuvwxyz')-2)

[ashwanin]

Use Keepchar(NOTE_TEXT)