Converting numeric Part No. to text

[Taser]

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.

 

[JW]

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.

 

[Dave Peterson]

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.

 

[ward376]

Have you tried a workbook text function like =RIGHT(A1,16)?

 

[Taser]

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.

 

[Dave Peterson]

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