Create a sequence when the first cell has text and numbers

This might be a silly question, but I haven't been able to find the
answer.  I have one cell that will always have the the following
format: ABCD1111111111111111 (4 letters and 16 numbers).  That will be
my first cell, I want to be able to create that number plus 1 on the
cell below, and repeat (scroll down) as many rows as there is
information on the left column.  I t can be either using a formula or a
macro:

	   A	         B
1	Apples	ABCD0000000000000005
2	Oranges	ABCD0000000000000006
3	Pears	ABCD0000000000000007
4	Coconut	ABCD0000000000000008

I tried Autofill but it doesn't work, and neither does =previous column
+1.

Help!

Thanks!

0
8/2/2006 2:51:08 PM
excel 39879 articles. 2 followers. Follow

5 Replies
396 Views

Similar Articles

[PageSpeed] 50

I put this in B1
="ABCD"&TEXT(ROW(A5),REPT("0",14))
and dragged down.

When I was done, I selected that column
edit|copy
edit|paste special|values
to change the formulas to values.

cfmartin76@gmail.com wrote:
> 
> This might be a silly question, but I haven't been able to find the
> answer.  I have one cell that will always have the the following
> format: ABCD1111111111111111 (4 letters and 16 numbers).  That will be
> my first cell, I want to be able to create that number plus 1 on the
> cell below, and repeat (scroll down) as many rows as there is
> information on the left column.  I t can be either using a formula or a
> macro:
> 
>            A             B
> 1       Apples  ABCD0000000000000005
> 2       Oranges ABCD0000000000000006
> 3       Pears   ABCD0000000000000007
> 4       Coconut ABCD0000000000000008
> 
> I tried Autofill but it doesn't work, and neither does =previous column
> +1.
> 
> Help!
> 
> Thanks!

-- 

Dave Peterson
0
petersod (12005)
8/2/2006 3:00:52 PM
WOW Dave, it works great!

Now a quick question, what if the number I want to start with is not
0000000000000001 but something like 0000010000000001?

Thanks!

0
8/2/2006 3:11:51 PM
wow dave, this is great.  Now, what if the first number is not
0000000000000001 but 0000100000000001 or something else?  can I start
the list with any random number?

0
8/2/2006 3:15:54 PM
="ABCD"&TEXT(100000000+ROW(A1),REPT("0",14))

change that big number to your starting point.

cfmartin76@gmail.com wrote:
> 
> wow dave, this is great.  Now, what if the first number is not
> 0000000000000001 but 0000100000000001 or something else?  can I start
> the list with any random number?

-- 

Dave Peterson
0
petersod (12005)
8/2/2006 3:20:45 PM
awesome, thank you SO much!

0
8/2/2006 3:37:10 PM
Reply:

Similar Artilces:

Cells font colors
Does anyone know how to have a cell font change color What I am trying to figure out is if cell A1 contains a number I would like that cell font to start off in red and if cell B2 contains a number have cell A1 change to red automatically I think it is something like =IF(D4="",0[RED],100[GREEN] And/Or Should I use Condiational Formating or a double IF? Hi Robbins2U! You can't do it with cell formulas. Take a look at Format > Conditional formatting. You need the Formula is option and your formula should evaluate to TRUE or FALSE -- Regards Norman Harker MVP (Excel) Sydn...

breaking text into multiple cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Lets say I type &quot;a b c d&quot; into a cell. Is there a way I can place the cursor between &quot;a&quot; and &quot;b&quot; and then cause only &quot;a&quot; to remain in the original cell while &quot;b c d&quot; move to a new cell? <br><br>Thanks! On 2010-05-27 12:53:39 -0400, Dwade01@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Lets say I type "a b c d" into a cell. Is ther...

Unsolvable cell naming question: Can anyone beat this one?
I am trying to copy and paste named cells from one workbook to another without having the first workbook file name attach to the named cells. For example, I am copying a cell named XYZ from file One.xls to file Two.xls. I need to have cell XYZ NOT say One.xls!XYZ when it is entered into Two.xls. Also, how can I get that message that asks if I want to use the cell names to not pop up? I think I understand what you're running into here. If the cell you are MOVING refers to a named range in it's source workbook that doesn't exist in the destination workbook, Excel will not ide...

Windows users cannot open attachments created in Office 2008
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I "upgraded" [HA!] from Office 2004 to Office 2008 in January. I soon discovered that Windows users could not open Word files I created (regardless of whether I saved them as .doc or .docx, and regardless of their ability to open .docx files.) I don't remember how I fixed the problem, and it's happening in Excel too. I wasted several hours trying to find a fix and I can't. Mac users can open the .xls file I sent, but Windows users can't. I had no trouble uploading the files to a Windows network d...

Two different totals in one cell
C D L O P Q R H, AL A, AL A A P SF H, AL 1.0, 1.0 1.0, 1.0 7.50 8.5 0.00 0.00 1.0, 2.0 As you can see, “A” (shown in columns D and L) and “AL” (shown in columns C and D) are in two separate columns; I would like to show their totals in two separate columns, i.e, O (totals of all “A”) and R (totals of “AL”) in the example above. Try these: =3DCOUNTIF(C1:L1,"*A*") =3DCOUNTIF(C1:L1,"*AL*") Hope this helps. Pete On May 4, 4:27=A0pm, something68 <somethin...@discussions.microsoft.com> wrote: > C =A0 =A0 =A0 D =A0 =A0 =A0 L =A0 =A0 =A0 O ...

Change the numeric value of a dropdown to text value
I'm trying to email the values of form fields that I have created in a form. So basically what I want to do is in the Subject I want to take the "value" of the dropdown called dropdown1 and put it in the subject. It is working but it is putting the numeric value instead of the text value. How do I convert it. Thanks much! Use the .Result, not the .Value -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Wo...

How to retrieve number of files
What's the correct way to retrieve the number of jpg files in a directory? Do I have to loop through each file, in order to count them up? >What's the correct way to retrieve the number of jpg files in a directory? >Do I have to loop through each file, in order to count them up? Yes - use FindFirstFile (or CFileFind) to filter on *.jpg and loop/count them. Dave Yes. use ::FindFirstFile/::FindNextFile/::FindClose or use the CFileFind class of MFC. joe On Sun, 4 Nov 2007 01:59:12 -0800, Charles Tam <CharlesTam@discussions.microsoft.com> wrote: >What's the ...

how do i create an action button in publisher?
ei am using publisher to creat email marketing publication and are looking to add interactive buttons. I have looked every where in the software, could not find it as easy as it is in powerpoint or access and excel? Is the function hidden somewhere? I have seen it used on one of the template in publisher, could someone help.I desperately need an answer. Please What kind of action to you want Publisher to perform when you click on said button? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "John" <John@discussio...

absolute cell references
I've got monthly expense numbers in one workbook I need to compare to monthly expense numbers in another workbook. I insert a new column to calculate the differences in one of the worksheets. Then I simply click to create the formula that subtracts the expenses in the current workbook from the expenses in the other workbook. Here's the problem. I'd like to set up one cell with the difference and then just copy it to fill in the rest of the months, right? Well, for some reason, when I create the first cell, it refers to the other workbook using an absolute cell refere...

When creating a toolbar manually...
Hello, I tried in my program to create a toolbar manually (I have a class that derives from CToolBar) instead of modifying what the app wizard generates for me. Everything works, except for one small thing: The string associated with each button is only displayed in the status bar while the button is being pressed, not when hovering above it. Can that be fixed easily somehow? This was just an experiment and it's a bit annoying that I could get everything working except when the button string is displayed in status bar. Here's how its created: void MyToolBar::Create(CWnd *parent...

how do you stop editing the text in a created button
i'm in the process of setting up a spreadsheet, which happens to include macros! now the only problem i have is everytime i press the relevant button i've created and assigned a macro to it goes straight in to the editing text mode! is there any way of stopping that! Sounds like a command button from the Control Toolbox. Open the Control Toolbox Toolbar and click on the design button to "exit design mode". This would be the top left button on the toolbar. Gord Dibben Excel MVP On Fri, 18 Nov 2005 08:18:13 -0800, "kara" <kara@discussions.microsoft.com>...

tool tip text in excel button
Hi, I have added button inside excel sheet(not vb control). Is it possible to add tooltip text for this? I am able to set tooltip text for vb controls. But i cannot use these controls inside excel sheet. Thanks and regards Duraiswamy ...

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

Memory Cell Could Not Be "READ."
I have Money 2K, WINXP Home, and until about a week ago I had no problems updating. Now, most of the time, when I try to update the Internet Information, I get an error message which reads: The instruction at "0x600bb01c" referenced memory at "0x00000007" The memory could not be "read." Click OK to terminate the program. When this happens, my modem lights flicker as if it is trying to get a response, but isn't getting one. After 10 seconds or so, I get the above message. Every few days it works fine. I have Fidelity as a investment, and I can always...

Creating Views
I am using an "if" function to return information to me from another worksheet, is there a way for the format of the original to be carried with the cell value? I'm trying to build a spreadsheet where I only enter data and format in one workseet then use the other "tabs" worksheets as "views".... Hi Doug a formula cannot transfer format information Frank Doug wrote: > I am using an "if" function to return information to me > from another worksheet, is there a way for the format of > the original to be carried with the cell value? ...

Select cell from range based on input in excel xp
I have a worksheet with a table of data in it. Based on user input generated from a pull down box that lists the values in the first column and another pulldown that lists the values in the first row of the table, how do I get the value of the field that is in the field where the row and column intersect? I want to read this value and use it in other formulas. Office XP Gary Once you get those values into the cells, you can use =index(match()) to return the value from the table. Debra Dalgleish's has some notes you may like: http://www.contextures.com/xlFunctions03.html dingy10...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

what is 'too many different cell formats' reply?
Tommy Read this KB article On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions.microsoft.com> wrote: Check these out: http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 http://www.vbapro.com/xl_formats.htm tj "tommy" wrote: > I think Gord meant to include this article link: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/?id=213904 Gord Dibben wrote: > Tommy > > Read this KB article > > > > On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions...

Creating a self deleting executable using .NET
I am trying to create an application for the Windows Mobile 5.0 (or higher) platform. One of the features of the application is that it has a panic button that automatically closes down the application and deletes the executable. I have been trying to find a technical solution for this feature but it has proven to be much harder than I thought. The problem is that the physical executable file is locked and can't be deleted as long as the executable is running. I found some quite interesting articles explaining the problem in more detail and also explaining some possibl...

Sequential numbers in a formula?
I have a few formulas (OFFSET & MATCH) which are the same (with relative references chanfing accordingly) and occupy cells B2:B500. In each formula one of the function arguments refers to a single cell in the adjacent column i.e A1:A500 which contains the numbers 1 to 500 (and the column is hidden as the numbers are needed only in the formulas and not to be displayed). The numbers are used to give the relative row number in the ranges used by OFFSET. So, the first formula uses 1 (via B2) and the second formula uses 2 (via B3) and so on. My question is: Can I somehow refer to a ...

Auto Adjusting Text Box
Is there any way to have the text box auto adjust the height in a protected form according to the amount of text a user inputs? Thanks! Jae ...

How do I change the size of a text form field box in Word?
I f more text is typed in the text form field box then its standard size the other form fill boxes to the right all move over to accommodate the additional text. Is there a way to increase the text form field box? Place your form fields inside (border-less) table cells whose dimensions are fixed. -- Stefan Blom Microsoft Word MVP "Blink" <Blink@discussions.microsoft.com> wrote in message news:383D7E9E-5A0E-4F3A-9586-CAC98B0CD776@microsoft.com... >I f more text is typed in the text form field box then its standard size >the > other form fill...

Creating forms.
Your question should have appeared here... If it had, my answer would have appeared here... On 17/01/08 1:44 AM, in article ee88d04.-1@webcrossing.caR9absDaxw, "hansonj1157@officeformac.com" <hansonj1157@officeformac.com> wrote: > -- Don't wait for your answer, click here: http://www.word.mvps.org/ Please reply in the group. Please do NOT email me unless I ask you to. John McGhie, Consultant Technical Writer McGhie Information Engineering Pty Ltd http://jgmcghie.fastmail.com.au/ Nhulunbuy, Northern Territory, Australia +61 4 1209 1410, mailto:john@mcghie.name...

Update field within a text box
I'm using Word 2007. I created a template with a REF field in a text box that refers back to a bookmark created with an ASK field earlier in the document. When I create a new document using this template, all of the ASK and FILLIN prompts come up, but the REF field in the text box does not update automatically. If I click in the text box and hit F9, it will update, but I would like it done automatically. Is this possible? TIA David No, even Ref fields that aren't in text boxes don't update automatically. You can go to Print Preview and back to update all the ...