Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
I have a column in my worksheet which contains 16-character part
numbers. Most of these part nos. have characters, and are formatted as text; however, a small portion of them are completely numeric and are recognized by Excel as numbers instead of text. My attempts to convert the entry into text for just these numerics has been fruitless. I've tried appending a single quote to the left, Str, and CellFormat, all in VBA, and all that happens is that they're turned to scientific notation, even though they show up as valid part nos. in the formula bar. Does anyone have any other ideas on how I could attack this problem? Tony R. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
This works fine for me:
Sub test1() Columns(1).EntireColumn.NumberFormat = "@" End Sub Then again, so does highlighting the entire column and setting the cell format to text. Taser wrote: I have a column in my worksheet which contains 16-character part numbers. Most of these part nos. have characters, and are formatted as text; however, a small portion of them are completely numeric and are recognized by Excel as numbers instead of text. My attempts to convert the entry into text for just these numerics has been fruitless. I've tried appending a single quote to the left, Str, and CellFormat, all in VBA, and all that happens is that they're turned to scientific notation, even though they show up as valid part nos. in the formula bar. Does anyone have any other ideas on how I could attack this problem? Tony R. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
First, if your data consists of 16 digits (no other characters) and you already
have it in excel, then your data is bad. Excel keeps track of 15 significant digits. Your 16 digit entries will always end with a 0--that 16th digit has been lost. You can format these number codes (Format|Cells|Number tab|Number (0 decimal places, and no 1000's separator) to see all the digits. If you preformat the cells as text, then anything you type in will be kept. You can also prefix your entry with an apostrophe: '1234123412341234 to treat your entry as text. But if that 16th digit is important, you'll have to spend some time fixing them. Taser wrote: I have a column in my worksheet which contains 16-character part numbers. Most of these part nos. have characters, and are formatted as text; however, a small portion of them are completely numeric and are recognized by Excel as numbers instead of text. My attempts to convert the entry into text for just these numerics has been fruitless. I've tried appending a single quote to the left, Str, and CellFormat, all in VBA, and all that happens is that they're turned to scientific notation, even though they show up as valid part nos. in the formula bar. Does anyone have any other ideas on how I could attack this problem? Tony R. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
Have you tried a workbook text function like =RIGHT(A1,16)?
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
The nature of the Part No. for these all-numeric ones is such that the
last two digits are always 00, so losing data because of Excel's significant digits limitation isn't that much of a problem. Still, while the conversion to number format turns it into a valid 16- character Part No., trying to set it from number to text turns it back to scientific notation. I was hoping to attach a single quote to the beginning (cell.Value = "'" & cell.Value) in a VBA function, but that gives me the scientific notation again. Looks like this part is doomed to be done manually. . . Tony R. On Oct 2, 4:29 pm, Dave Peterson wrote: First, if your data consists of 16 digits (no other characters) and you already have it in excel, then your data is bad. Excel keeps track of 15 significant digits. Your 16 digit entries will always end with a 0--that 16th digit has been lost. You can format these number codes (Format|Cells|Number tab|Number (0 decimal places, and no 1000's separator) to see all the digits. If you preformat the cells as text, then anything you type in will be kept. You can also prefix your entry with an apostrophe: '1234123412341234 to treat your entry as text. But if that 16th digit is important, you'll have to spend some time fixing them. Taser wrote: I have a column in my worksheet which contains 16-character part numbers. Most of these part nos. have characters, and are formatted as text; however, a small portion of them are completely numeric and are recognized by Excel as numbers instead of text. My attempts to convert the entry into text for just these numerics has been fruitless. I've tried appending a single quote to the left, Str, and CellFormat, all in VBA, and all that happens is that they're turned to scientific notation, even though they show up as valid part nos. in the formula bar. Does anyone have any other ideas on how I could attack this problem? Tony R. -- Dave Peterson- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numeric Part No. to text
You could use a worksheet formula:
=""&a1 to force the value to text then copy|paste special|values over the original range. Or maybe a macro like: Option Explicit Sub testme01() Dim myCell As Range Dim myRng As Range Set myRng = Selection For Each myCell In myRng.Cells With myCell .NumberFormat = "@" 'text .Value = Format(.Value, String(16, "0")) End With Next myCell End Sub Taser wrote: The nature of the Part No. for these all-numeric ones is such that the last two digits are always 00, so losing data because of Excel's significant digits limitation isn't that much of a problem. Still, while the conversion to number format turns it into a valid 16- character Part No., trying to set it from number to text turns it back to scientific notation. I was hoping to attach a single quote to the beginning (cell.Value = "'" & cell.Value) in a VBA function, but that gives me the scientific notation again. Looks like this part is doomed to be done manually. . . Tony R. On Oct 2, 4:29 pm, Dave Peterson wrote: First, if your data consists of 16 digits (no other characters) and you already have it in excel, then your data is bad. Excel keeps track of 15 significant digits. Your 16 digit entries will always end with a 0--that 16th digit has been lost. You can format these number codes (Format|Cells|Number tab|Number (0 decimal places, and no 1000's separator) to see all the digits. If you preformat the cells as text, then anything you type in will be kept. You can also prefix your entry with an apostrophe: '1234123412341234 to treat your entry as text. But if that 16th digit is important, you'll have to spend some time fixing them. Taser wrote: I have a column in my worksheet which contains 16-character part numbers. Most of these part nos. have characters, and are formatted as text; however, a small portion of them are completely numeric and are recognized by Excel as numbers instead of text. My attempts to convert the entry into text for just these numerics has been fruitless. I've tried appending a single quote to the left, Str, and CellFormat, all in VBA, and all that happens is that they're turned to scientific notation, even though they show up as valid part nos. in the formula bar. Does anyone have any other ideas on how I could attack this problem? Tony R. -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting time from text to numeric | Excel Worksheet Functions | |||
converting text to numeric data | Excel Worksheet Functions | |||
Convert numeric part of text to number | Excel Programming | |||
Converting Text into a Numeric Value and Totalling | Excel Worksheet Functions | |||
Converting Text to Numeric | Excel Programming |