I created a program for sending out dunning letters for past due invoices. The program evaluates past due invoices and generates cover letters and accompanying invoices then merges the cover letter and invoice(s) as a single PDF. At that time the program emails the PDF to RCFax.com who then, in turn, faxes the dunning letters to the customers. How we tell RC fax whom to send the letters to is by having the fax number @rcfax.com like 9999999999@rcfax.com.

My question is, is there any way to convert the text string in our ERP to show only numerical values? I have a series of "Replace" functions removing such things as parenthesis and hyphens, however, I've started noticing additional characters I'd like removed. I tried creating a larger series of replace functions to remove all non-numeric characters, however this quickly became too "complex" to perform in a query. Some examples of fax number formating are:

(999) 999-9999
(999) 999-9999 A/P
999.999.9999
(999) 999-9999 (PAT)

I know ideally an ERP should catch these formating issues when fax numbers were entered, however, these fax numbers are located in the AR rep's account notes and correct formating was not a big issue until now. I keep thinking the easiest way to remove all characters should be if there was something like a Val function that returned only number values in a string, out of a text string with other characters. Anybody else had to deal with this?

Thanks,
Joshua

Hi Joshua,

You've posted your question on the MS Excel board. Is there an Excel question hidden in there somewhere or do you need your thread to be moved to the MS Access board?

Cheers