Eliminating text from A Field

[DSX]

I have a CSV file where i need to remove the text and have no idea on if its possible I have only been using Access for 2 months so probably easy I hope.

Eg
there are 3 Cols
Local amount 1,234,345 EUR
Group Amount $ 2,034,237.32
Transaction Amount 1,234,345 EUR

I can lose the Transaction Amount but must have Local And Group amounts

This file is a CSV file with approx 150,000 records that will change daily therefore I need to have this as a linked file rather than imported

Any suggestions would be grateful as I am really stuck

 

[Andrew Fergus]

This should be able to be done but we will need some more information.

Is the sample data you gave a complete picture? For example, do you only want to extract the values for the lines starting with 'Local' and 'Group'? Does the data have the dollar symbol or not? Or is it as you have shown in your sample, in that it is sometimes there? Is the data currently held in one text field in your table? Also, when does the currency show? You have shown it for 2 lines but not the other. Lastly, are there currencies other than the Euro?

Andrew

 

[Gates Is Antichrist]

Each of those are essential issues that you need to address - and I'll add that eliminating the text will burn the distinction between local and group amounts, right? It's easy to do, but I think that's a fair preliminary question

 

[DSX]

I can lose the $,£ and EUR as Originating Company will identify if it was a euro

Thanks

 

[Andrew Fergus]

Hmmm, not a lot to go on there ........

Have a read of this thread - it shows you how to extract part of a text field using the 'Mid' and 'Instr' functions.