Formatting Cells for Quick Input of Data
 

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

Formatting Cells for Quick Input of Data
 

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" wrote in message
...
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

Formatting Cells for Quick Input of Data
 

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 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

Formatting Cells for Quick Input of Data
 

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

Formatting Cells for Quick Input of Data
 

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" wrote in message
...
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

Formatting Cells for Quick Input of Data
 

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JG" wrote in message
...
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" wrote in message
...
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

Formatting Cells for Quick Input of Data
 

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

Formatting Cells for Quick Input of Data
 

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


--

Dave Peterson