(Msg. 1) Posted: Wed Oct 29, 2008 5:54 am
Post subject: Formatting Cells for Quick Input of Data Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I need to enter over 200 serial numbers into Excel.
They all start with the same 6 characters and all end in the same 3 like so:
E33213**0P5
Can I format the cells so that I only have to input the 2 middle characters
and Excel will auto fill the other information?
The 2 characters I need to enter are letters, not numbers.
I tried the following without any luck:
E33213??0P5
E33213##0P5
(Msg. 2) Posted: Wed Oct 29, 2008 6:15 am
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Formatting will not change the underlying value, so if you want to use
that serial number for other purposes you need a different approach.
Suppose you enter the 2 middle characters into column A - then you can
put this formula in B1:
=IF(A1="","","E33213" &A1& "0P5")
and copy this down as far as you need. Then, once you have finished
entering your codes into column A, you can fix the values in column B
and then dispense with column A (assuming this is a one-off task).
Hope this helps.
Pete
On Oct 29, 12:54 pm, JG <J... DeleteThis @discussions.microsoft.com> wrote:
> I need to enter over 200 serial numbers into Excel.
>
> They all start with the same 6 characters and all end in the same 3 like so:
> E33213**0P5
>
> Can I format the cells so that I only have to input the 2 middle characters
> and Excel will auto fill the other information?
>
> The 2 characters I need to enter are letters, not numbers.
>
> I tried the following without any luck:
> E33213??0P5
> E33213##0P5
(Msg. 3) Posted: Wed Oct 29, 2008 8:23 am
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You could use a custom format of:
"E33213"@"OP5"
It'll be up to you to type exactly two characters.
And the value of the cell will not include those special formatting characters.
JG wrote:
>
> I need to enter over 200 serial numbers into Excel.
>
> They all start with the same 6 characters and all end in the same 3 like so:
> E33213**0P5
>
> Can I format the cells so that I only have to input the 2 middle characters
> and Excel will auto fill the other information?
>
> The 2 characters I need to enter are letters, not numbers.
>
> I tried the following without any luck:
> E33213??0P5
> E33213##0P5
(Msg. 4) Posted: Wed Oct 29, 2008 6:35 pm
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Hi,
Say you are entering the numbers in column A. In column B, enter the
following formula
="E33213"&A1&"0P5" and copy down.
Once you have copied down below, highlight the range and copy > Paste
Special > Values. Now you can delete all the entries from column A.
"JG" <JG DeleteThis @discussions.microsoft.com> wrote in message
news:73A1876F-D545-426B-AA43-A186F025BC66@microsoft.com...
> I need to enter over 200 serial numbers into Excel.
>
> They all start with the same 6 characters and all end in the same 3 like
> so:
> E33213**0P5
>
> Can I format the cells so that I only have to input the 2 middle
> characters
> and Excel will auto fill the other information?
>
> The 2 characters I need to enter are letters, not numbers.
>
> I tried the following without any luck:
> E33213??0P5
> E33213##0P5
(Msg. 5) Posted: Wed Oct 29, 2008 6:35 pm
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks! That worked perfectly!!!
"Ashish Mathur" wrote:
> Hi,
>
> Say you are entering the numbers in column A. In column B, enter the
> following formula
>
> ="E33213"&A1&"0P5" and copy down.
>
> Once you have copied down below, highlight the range and copy > Paste
> Special > Values. Now you can delete all the entries from column A.
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com >
> "JG" <JG.RemoveThis@discussions.microsoft.com> wrote in message
> news:73A1876F-D545-426B-AA43-A186F025BC66@microsoft.com...
> > I need to enter over 200 serial numbers into Excel.
> >
> > They all start with the same 6 characters and all end in the same 3 like
> > so:
> > E33213**0P5
> >
> > Can I format the cells so that I only have to input the 2 middle
> > characters
> > and Excel will auto fill the other information?
> >
> > The 2 characters I need to enter are letters, not numbers.
> >
> > I tried the following without any luck:
> > E33213??0P5
> > E33213##0P5
>
(Msg. 6) Posted: Wed Oct 29, 2008 7:44 pm
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"JG" <JG DeleteThis @discussions.microsoft.com> wrote in message
news:0B08B4CD-8CB3-47BC-8D49-1922591FC4D8@microsoft.com...
> Thanks! That worked perfectly!!!
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Say you are entering the numbers in column A. In column B, enter the
>> following formula
>>
>> ="E33213"&A1&"0P5" and copy down.
>>
>> Once you have copied down below, highlight the range and copy > Paste
>> Special > Values. Now you can delete all the entries from column A.
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com >>
>> "JG" <JG DeleteThis @discussions.microsoft.com> wrote in message
>> news:73A1876F-D545-426B-AA43-A186F025BC66@microsoft.com...
>> > I need to enter over 200 serial numbers into Excel.
>> >
>> > They all start with the same 6 characters and all end in the same 3
>> > like
>> > so:
>> > E33213**0P5
>> >
>> > Can I format the cells so that I only have to input the 2 middle
>> > characters
>> > and Excel will auto fill the other information?
>> >
>> > The 2 characters I need to enter are letters, not numbers.
>> >
>> > I tried the following without any luck:
>> > E33213??0P5
>> > E33213##0P5
>>
(Msg. 7) Posted: Thu Oct 29, 2009 9:10 am
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I had a similar question the other day but cannot find my post...go figure!
Anyway, your custom formatting example is EXACTLY the info I was looking
for!!! This is absolutely awesome, quick, no extra steps required...PERFECT!
Thank you so much for posting.
"Dave Peterson" wrote:
> You could use a custom format of:
> "E33213"@"OP5"
>
> It'll be up to you to type exactly two characters.
>
> And the value of the cell will not include those special formatting characters.
>
> JG wrote:
> >
> > I need to enter over 200 serial numbers into Excel.
> >
> > They all start with the same 6 characters and all end in the same 3 like so:
> > E33213**0P5
> >
> > Can I format the cells so that I only have to input the 2 middle characters
> > and Excel will auto fill the other information?
> >
> > The 2 characters I need to enter are letters, not numbers.
> >
> > I tried the following without any luck:
> > E33213??0P5
> > E33213##0P5
>
> --
>
> Dave Peterson
>
(Msg. 8) Posted: Thu Oct 29, 2009 1:22 pm
Post subject: Re: Formatting Cells for Quick Input of Data [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
This gives ok results to look at, but remember that the value in cell isn't
always what you see!
It can make divising formulas more difficult along with Edit|Find's...I know
it's there, I can see it...why can't excel find it...
Cheri wrote:
>
> I had a similar question the other day but cannot find my post...go figure!
>
> Anyway, your custom formatting example is EXACTLY the info I was looking
> for!!! This is absolutely awesome, quick, no extra steps required...PERFECT!
> Thank you so much for posting.
>
> "Dave Peterson" wrote:
>
> > You could use a custom format of:
> > "E33213"@"OP5"
> >
> > It'll be up to you to type exactly two characters.
> >
> > And the value of the cell will not include those special formatting characters.
> >
> > JG wrote:
> > >
> > > I need to enter over 200 serial numbers into Excel.
> > >
> > > They all start with the same 6 characters and all end in the same 3 like so:
> > > E33213**0P5
> > >
> > > Can I format the cells so that I only have to input the 2 middle characters
> > > and Excel will auto fill the other information?
> > >
> > > The 2 characters I need to enter are letters, not numbers.
> > >
> > > I tried the following without any luck:
> > > E33213??0P5
> > > E33213##0P5
> >
> > --
> >
> > Dave Peterson
> >
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office