4, 5, and 6 digit numbers should be text

[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

[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!
=====================================================

"Jim May" <[email protected]> wrote in message
news:Uzhng.43177$fG3.7188@dukeread09...
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

[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

"RagDyeR" <[email protected]> wrote in message
news:#[email protected]:

> 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!
> =====================================================
>
> "Jim May" <[email protected]> wrote in message
> news:Uzhng.43177$fG3.7188@dukeread09...
> 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

[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

"RagDyeR" <[email protected]> wrote in message
news:#[email protected]:

> 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!
> =====================================================
>
> "Jim May" <[email protected]> wrote in message
> news:Uzhng.43177$fG3.7188@dukeread09...
> 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

[David Biddulph]

"Jim May" <[email protected]> wrote in message
news:c4yng.43256$fG3.13678@dukeread09...
> 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?

Paste Special/ Values or
Paste Special/ Formulas

In either case, the destination's formatting is preserved.
--
David Biddulph