I have been asked to convert an alphanumeric field where the alph character represents a number and identifies the whole field as a negative number. I'll try explain by example. Colum one below is the original alpha numeric field and the second colum is what the first clum repesents in numeric form. I have been able to do a simple replace function and divide the result by "-1" to get the negative number desired. See below
0000000}
0000001}
0000010}
My problem is that the data in column one has several alpha characters each representing a different number. See below the result I am after.
0000001}
0000001J
0000001K
The replacement function for the first example looks like this:
((Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))
But I don't know how to put more than one replace funtion together. And to make matters worse there are records with no alpha characters which represent positive numbers: see an example below:
0000001}
00000010
0000001K
Where this becomes a problem is when I divide by "-1" Obviously I only want to divide by "-1" when there is an alph character in the field I am converting.
I am hoping one or more of you have a solution as I have exhausted my limited knowledge and search avenues for a solution.
Thank you,
Chip
0000000}
-1
-10
-100
My problem is that the data in column one has several alpha characters each representing a different number. See below the result I am after.
0000001}
-10
-11
-12
The replacement function for the first example looks like this:
((Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))
But I don't know how to put more than one replace funtion together. And to make matters worse there are records with no alpha characters which represent positive numbers: see an example below:
0000001}
-10
10
-12
Where this becomes a problem is when I divide by "-1" Obviously I only want to divide by "-1" when there is an alph character in the field I am converting.
I am hoping one or more of you have a solution as I have exhausted my limited knowledge and search avenues for a solution.
Thank you,
Chip