| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| input data dependent on other cells | Excel Worksheet Functions | |||
| is there a quick easy way to input time? | Excel Worksheet Functions | |||
| specify a range of cells for data input, down then over | Excel Discussion (Misc queries) | |||
| Data input in cells | Excel Discussion (Misc queries) | |||
| Is there a quick shortcut to swap the data in two cells? | Excel Discussion (Misc queries) | |||
Linear Mode
