Solve : Truncating Numbers in Excel?

1.	Solve : Truncating Numbers in Excel?
Answer» Hi All. I am trying to create a form that records data input from a barcode reader. The problem that I have is that the original number on the barcode label contains 12 digits but I only require the last 6 to be displayed in my form. I have tried to re-configure the reader to truncate the number to 6 digits but this doesn't work so I was wondering if anyone can tell me whether there is a way to do this using Excel or a macro. I am using Excel 2010. Thank yoYou NEED to claify what you want to do. In Excel, the term 'truncate' is USED to remove decimals that are not needed. https://exceljet.net/excel-functions/excel-trunc-function However, it may be taht what you want is not that. Give some example of before and after. RIGHT will do this for you. so macro code will look SOMETHING like this; last6 = Right(mybarcode, 6)Mark, that will work if it is a string. He did not say if the reade outputs text or a real number. Quote from: Geek-9pm on December 28, 2017, 08:42:54 PM Mark, that will work if it is a string. He did not say if the reade outputs text or a real number. -Barcode readers typically input text into the computer by emulating a keyboard. It will literally type the number digits into the computer when scanned. This would be the only way supported within Excel. -representing BARCODES as numeric types would be INEFFECTIVE as any sort of validation would need to convert it to a string to perform a number of string operations. -Even if the cell type in excel is numeric- which would be the likely default, It doesn't matter what type of data is in the cell. Excel will coerce the data to a text string if necessary. =RIGHT(ATAN(1)*4,7) gives "5358979" for example. -We know the cell type is almost certainly not numeric as a 12-digit numeric value will be shown via scientific notation which is useless as a barcode. You are right. a 12 digit real number would be way beyhound normal usage.Just in case you may have leading or trailing spaces in your barcode input, I would use this: last6 = Right(trim(mybarcode), 6)

Answer»

Hi All. I am trying to create a form that records data input from a barcode reader. The problem that I have is that the original number on the barcode label contains 12 digits but I only require the last 6 to be displayed in my form. I have tried to re-configure the reader to truncate the number to 6 digits but this doesn't work so I was wondering if anyone can tell me whether there is a way to do this using Excel or a macro. I am using Excel 2010.
Thank yoYou NEED to claify what you want to do.
In Excel, the term 'truncate' is USED to remove decimals that are not needed.
https://exceljet.net/excel-functions/excel-trunc-function

However, it may be taht what you want is not that.
Give some example of before and after.
RIGHT will do this for you.

so macro code will look SOMETHING like this;
last6 = Right(mybarcode, 6)Mark, that will work if it is a string.
He did not say if the reade outputs text or a real number.
Quote from: Geek-9pm on December 28, 2017, 08:42:54 PM

Mark, that will work if it is a string.
He did not say if the reade outputs text or a real number.

-Barcode readers typically input text into the computer by emulating a keyboard. It will literally type the number digits into the computer when scanned. This would be the only way supported within Excel.

-representing BARCODES as numeric types would be INEFFECTIVE as any sort of validation would need to convert it to a string to perform a number of string operations.

-Even if the cell type in excel is numeric- which would be the likely default, It doesn't matter what type of data is in the cell. Excel will coerce the data to a text string if necessary. =RIGHT(ATAN(1)*4,7) gives "5358979" for example.

-We know the cell type is almost certainly not numeric as a 12-digit numeric value will be shown via scientific notation which is useless as a barcode.
You are right. a 12 digit real number would be way beyhound normal usage.Just in case you may have leading or trailing spaces in your barcode input, I would use this:
last6 = Right(trim(mybarcode), 6)

Solve : Truncating Numbers in Excel?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment