Formatting Cells for Quick Input of Data

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

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
 
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
 
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
 
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.
 
Thanks! That worked perfectly!!!

Ashish Mathur said:
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
Click to expand...
 
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.
 
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...
 
Back
Top