4, 5, and 6 digit numbers should be text

J

Jim May

In an application I have going I refer to a field
"Location", here are (samples):
4133
51053
617111

All are strict location numbers and no math
Will ever be performed using them..

I use Vlookup() formulas which use them
extensively. A recent "quick-programming-change"
I made recently caused me (to make it work) take
Several of these fields and enter the ' character
In front of to get it to work
'4133
'51053
'617111

I always stay clear of the Format such as Text as
I know it doesn't really make the cell value (4133) actually text.

For data entry is there some way I can enter 4133 alone
Without the leading ' and have it register to Excel as Text
So that it can be read in a Vlookup() without having to
Deal with the first ISERR() argument?

Thanks in advance,

Jim May
 
R

RagDyeR

Well ... don't stay away from the 'Text' format any longer!

*Pre*format the range to "Text", and see what happens.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

In an application I have going I refer to a field
"Location", here are (samples):
4133
51053
617111

All are strict location numbers and no math
Will ever be performed using them..

I use Vlookup() formulas which use them
extensively. A recent "quick-programming-change"
I made recently caused me (to make it work) take
Several of these fields and enter the ' character
In front of to get it to work
'4133
'51053
'617111

I always stay clear of the Format such as Text as
I know it doesn't really make the cell value (4133) actually text.

For data entry is there some way I can enter 4133 alone
Without the leading ' and have it register to Excel as Text
So that it can be read in a Vlookup() without having to
Deal with the first ISERR() argument?

Thanks in advance,

Jim May
 
J

Jim May

RD,

*Pre*format the range to "Text", and see what happens.
<< Yes, I've seen this before, but I didn't buy the farm,
although perhaps I should (have).

I've ben using this Macro, of late to accompdate things..

Sub AddApostrophe()
For Each c In Selection
c.Value = "'" & c.Value
Next c
End Sub
 
J

Jim May

RD,

I've been thinking more about this, and the problem
Is that although one Pre-formats a range as Text,
If (and you often do) you copy numbers from another
Range and then paste them into the Pre-formatted
Range, it's formatting (the destination range) gets
"blown-away" and receives the Source formatting,
So there you go - there needs to be perhaps a Paste-As-Text
Command.
Any thoughts?

Tks again,
Jim
 
D

David Biddulph

Jim May said:
RD,

I've been thinking more about this, and the problem
Is that although one Pre-formats a range as Text,
If (and you often do) you copy numbers from another
Range and then paste them into the Pre-formatted
Range, it's formatting (the destination range) gets
"blown-away" and receives the Source formatting,
So there you go - there needs to be perhaps a Paste-As-Text
Command.
Any thoughts?
Click to expand...

Paste Special/ Values or
Paste Special/ Formulas

In either case, the destination's formatting is preserved.
 
Top