Excel to Access-disappearing numbers saved as text

Microsoft Outlook 2013 32/64-bit (mail m...
October 1, 2018 at 09:59:42
Specs: Windows 64
To clarify, we have account numbers of 15 characters and when they come to us from other reports in Excel, they're sometimes saved as text. When we've 'converted to numbers', we get the extra '.00' at the end so we'll format the cells, removing the ".00" but it rounds the number so we lose the last character of the account number.

We need to move these numbers into our Access database but the numbers saved as text don't show up.

Has anyone seen this error before?


See More: Excel to Access-disappearing numbers saved as text

Reply ↓  Report •

#1
October 1, 2018 at 10:52:03
We need to move these numbers into our Access database but the numbers saved as text don't show up.

Sounds like an Access problem.
Do not know a lot about Access, but how is the field in Access defined?
Change it to Text.

Your not doing any calculations on the account number are you?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
October 1, 2018 at 12:10:00
The field is set for text in Access and no, we aren't doing any calculations in that field. We're literally just taking an Excel spreadsheet and sending it to our database for the reports in Access.

The issue could be treated as two-fold with the first part being Excel. If we have a 16 digit account number, we'd prefer to not have those little green traingles on the corner of the cells. We can get rid of it by either 'convert to number' or by 'ignore the error.' but we still have problems, like with Excel rounding our number even though it says it's text.

Is there a way to stop rounding?


Reply ↓  Report •

#3
October 1, 2018 at 13:06:38
How is the account number being created, input directly into Excel or being copied in some manner?

Is the cell Formatted as Text before data is entered?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
October 4, 2018 at 08:42:54
I can't answer those questions, Mike. I work in a multi-national company and have no idea even what application these numbers are coming from. How many hands have touched a report before I get it is unknown. I can only tell you when it comes to me, it's text.

Oh, typo in the original questions! The account numbers are sixteen characters, not fifteen, which is why they're text. Excel seems to stop taking numbers at 15 and everything bigger is rounded up or down. I can't have that. Think of it like a credit card which is 16 characters. If your bank doesn't have that 16th character....well, it's not your account.

So keeping the number in text is causing it to disappear when we enter it into Access. The Access field was changed to text/number to accommodate this but it doesn't seem to like these numbers.

Is there anyway in Excel to keep the 16 characters AND leave it as a number without it rounding off?


Reply ↓  Report •

#5
October 4, 2018 at 10:53:29
I don't know Access at all so this may be way out in left field. (no pun intended)

What about splitting the text string into 2 numbers in separate cells in Excel, transferring the numbers into 2 fields in Access and then concatenating them back together once they appear in Access?

I'm a bit confused as to how the 16 digits are being entered in Excel now, but you say "I can only tell you when it comes to me, it's text." If they are already stored as 16 character text strings in Excel, and all 16 characters are single digits numbers, then you can split the string into two 8 digit numbers using:

=LEFT(A1,8)*1 and =RIGHT(A1,8)*1

Excel is friendly like that. Even though the LEFT and RIGHT functions create text stings, if Excel can perform the math operation (*1) it will convert the resulting strings to numbers.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 4, 2018 at 11:13:23
So keeping the number in text is causing it to disappear when we enter it into Access

Still sounds like an Access problem.

Is there anyway in Excel to keep the 16 characters AND leave it as a number without it rounding off?

Not in a single cell.

You could break the number apart into two cells and then transfer into Access as two cells.
If you try to combine them in Excel the resulting cell will be a TEXT cell, so

A1 = 1234567890
B1 = 123456
C1 = A1&B1 <<< Will display as TEXT not a Number.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
October 4, 2018 at 11:21:36
In Access, has the Field been defined with sufficient length to accept 16 characters?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#8
October 4, 2018 at 11:27:32
Okay, the idea to split the number into two cells isn't bad....but what if it's more of less than 16 characters? We do still have some old accounts which are only 9 characters and this 16 digits came out of the blue (right hand doesn't wash the left and we had no idea at all that our formulas were about to fail!) Anyway, if finance decides they need a 20 character string, we don't want to get caught. Is there a "remainder" command? Like Left for the first 10, 12, 15 characters and then _______ for what's left?

Reply ↓  Report •

#9
October 4, 2018 at 11:54:28
old accounts which are only 9 characters and this 16 digits came out of the blue

Do the OLD 9 characters account numbers transfer OK?

Make sure that who ever is in charge of the Access side knows that the NEW account
numbers are 16 digits.
Could be that is where your problem lies.

Also, another thought is extraneous material, like non-print characters.
Use the CLEAN() function to remove any if they exist.

The CLEAN function was designed to remove the first 32 non-printing characters in the 7-bit
ASCII code (values 0 through 31) from text.

You can combine the CLEAN() function with the TRIM() function

TRIM - removes all spaces at the start of text, more than one space between words, and spaces at the end of text.

So something like: =CLEAN(TRIM(A1))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#10
October 4, 2018 at 12:19:25
if finance decides they need a 20 character string

Here is a modification of DD3's LEFT & RIGHT:

=LEFT(A1,(LEN(A1)/2))*1

=RIGHT(A1,(LEN(A1)/2))*1

We simply get the length of cell A1 & divide by 2
Should be good for up to 20 characters in cell A1

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#11
October 4, 2018 at 12:31:57
Make sure that who ever is in charge of the Access side knows that the NEW account
numbers are 16 digits.

Yes, she knows, she sits beside me. And this is an existing database with only the new 16 character accounts having the issue.

We'll try the LEN and see what happens


Reply ↓  Report •

#12
October 4, 2018 at 14:25:23
old accounts which are only 9 characters and this 16 digits came out of the blue

Do the OLD 9 characters account numbers transfer OK?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
October 5, 2018 at 07:32:40
Do the OLD 9 characters account numbers transfer OK?

this is an existing database with ONLY the new 16 character accounts having the issue.


Reply ↓  Report •

#14
October 5, 2018 at 07:48:52
Does any string length of characters work, alpha or numeric?

We know what does not work, so what does work?
Might give a clue as to what is going on.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#15
October 5, 2018 at 08:28:26
Okay, 9 characters, 10 characters, 12 characters, and 15 characters. These are all numeric.

Then alpha-numeric 9 and 10 characters work.


Reply ↓  Report •

#16
October 5, 2018 at 09:00:02
9 characters, 10 characters, 12 characters, and 15 characters. These are all numeric.

OK, so it works up to 15 characters.
I'm wondering if Access has the same 15 number limitation as Excel?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#17
October 5, 2018 at 09:32:15
Hunted around a bit, and as best as I can tell there is no 15 number limit in Access.
So why it won't accept your account numbers is still a mystery.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#18
October 5, 2018 at 10:34:23
It shouldn't. If we try to move it over as text, Access will allow 255 characters into a cell.

Reply ↓  Report •

#19
October 5, 2018 at 11:36:18
we'd prefer to not have those little green traingles on the corner of the cells. We can get rid of it by either 'convert to number' or by 'ignore the error.' but we still have problems

To remove both the little green triangle and the small error box:

Go to Excel Options
Select Formula
In the Error Checking Rules section,
Un-check the: Numbers Formatted as text or preceded by an apostrophe box

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#20
October 5, 2018 at 12:12:33
In regards to the CLEAN() & TRIM suggestion in #9,
there is one other non-printable character that CLEAN() does not work on,
the non-breaking space character, ASCII code 160,
so in addition to CLEAN() & TRIM() try this also:

=SUBSTITUTE(A1,CHAR(160),"")

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#21
October 9, 2018 at 10:59:42
While poking around the web found this thread that you might find interesting.

https://stackoverflow.com/questions...

Seems your not the only one with disappearing data.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Ask Question