Translating "coded" import data

  • Thread starter Thread starter mickeyd
  • Start date Start date
M

mickeyd

I have to create a spreadsheet from imported data. Since the data i
sensitive, the dollar amounts (all in one column) are coded by the las
character. I have the legend which translates what characters/letter
correspond to what numbers. For example, F as the last characte
equals 6, so 3045F is 304.56. How can I format this column, eithe
during import or after, to translate the data to the proper amounts
 
Assuming "j" represents 0:

=SUBSTITUTE(A1,RIGHT(A1,1),MATCH(RIGHT(A1,1),{"j";"a";"b";"c";"d";"e";"f";"g
";"h";"i"},0)-1)/100

should translate the contents of A1.

--

Vasant

mickeyd said:
I have to create a spreadsheet from imported data. Since the data is
sensitive, the dollar amounts (all in one column) are coded by the last
character. I have the legend which translates what characters/letters
correspond to what numbers. For example, F as the last character
equals 6, so 3045F is 304.56. How can I format this column, either
during import or after, to translate the data to the proper amounts?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top