UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Converting And Rounding Numbers Stored As Text, Any Version    
 
   
likajoho
post Jan 13 2020, 11:54 AM
Post#1



Posts: 1,075
Joined: 18-February 04
From: Oklahoma


Goal: convert "age */12", into "*/12" and then into a year based on the document year - the age. So four columns involved.
BH**** is age in text format - i.e. "age 3/12"
BJ**** is age without any letters or other characters - i.e "3/12"
AJ**** is document/event year - i.e. "1900"
CB**** is estimated birth year - i.e. "1900"
All columns are formatted "General"

Background: In calculating birth years, I start with an age, which is sometimes a fraction. To keep it from converting to a decimal or other number, I add the word "age" before it. [this columns also includes other non number age identifiers such as "<", ">", "abt", "est".

I added the column BJ*** in order to removed any letters and having being doing so by hand. I put the fraction into decimal format using the round function.

CB then uses BJ and AJ to calculate the estimated birth year.

I want to create a formula for the BJ column to make the calculation automatic. It sounded easy, and seemed to work, but it wouldn't round. I suspect the problem is related to the format of the column not wanting to treat the contents as numbers, but how can I get around that? I really don't want to change the format of the column because I often copy and paste and reuse them.

I know to remove the "age " I simply can use the formula "=RIGHT(BH293,LEN(BH293)-4)". The extra calculation accommodates ages of different lengths. The result is simply a removal of the "age " from the text so that "age 1/12" becomes simply "1/12"

But I can't get the round to work with that formula. I've tried using the value format and other things, but to no avail. So I resigned myself to using the fraction. I'm OK with that for this column. Actually I prefer it.

So I decided I would do the rounding in the CB column when the year is calculated, but I end up with a number like "-42144".

I've tried everything I can think of. The formula I used before trying to automate the calculation was "=ROUND(AJ293-BJ293,0)" which worked fine when BJ*** looked like a decimal, but doesn't work when the BJ*** is a fraction.

Any suggestions for a formula without changing the format of the columns? This affects only about 10% of the table.
Go to the top of the page
 
June7
post Jan 13 2020, 01:08 PM
Post#2



Posts: 1,181
Joined: 25-January 16
From: The Great Land


Access VBA has Eval() function. It will take a string like "3/12" and do the arithmetic represented by the / character to divide 3 by 12.

For possible Excel equivalent review https://www.myonlinetraininghub.com/excel-f...aluate-function

This post has been edited by June7: Jan 13 2020, 01:09 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
likajoho
post Jan 14 2020, 09:59 AM
Post#3



Posts: 1,075
Joined: 18-February 04
From: Oklahoma


Thanks for suggestion, but couldn't find "Evaluate" as a function in current version of MS EXcel.
Go to the top of the page
 
June7
post Jan 14 2020, 02:57 PM
Post#4



Posts: 1,181
Joined: 25-January 16
From: The Great Land


You aren't supposed to. Did you follow tutorial to the end?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th January 2020 - 01:16 AM