Series including text possible?

Is there a better way to create a "series" that is based on characters
as well as digits than what I'm doing below?

I created 3 columns.  I put the text in one, the digits in the second
that I could apply the series to, with the 3rd column holding a ":".
But when I paste into the text file, I have tabs separating the 3
columsn where I was hoping for no space.

So this here -

NAQAHDAH	01	:
NAQAHDAH	02	:
NAQAHDAH	03	:

should paste like this -
NAQAHD6H01:
NAQAHDAH02:
NAQAHDAH03:
NAQAHDAH04:
NAQAHDAH05:
NAQAHDAH06:
NAQAHDAH07:
NAQAHDAH08:
NAQAHDAH09

Is there perhaps a way to do this while keeping the 3 elements
together in each cell of the column?  Or perhaps there's another way?

Thanks. :oD

0
StargateFan
4/7/2007 7:28:46 PM
excel 39879 articles. 2 followers. Follow

3 Replies
418 Views

Similar Articles

[PageSpeed] 37

If you start in Row 1, use this formula:

="NAQAHDAH"&TEXT(ROW(),"0000")&":"

Fill down as far as you need

Use ROW()-1 if you start in Row 2, etc

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message news:kfrf1393vtsvqm2ffh4dtkc667ejlk75ao@4ax.com...
| Is there a better way to create a "series" that is based on characters
| as well as digits than what I'm doing below?
|
| I created 3 columns.  I put the text in one, the digits in the second
| that I could apply the series to, with the 3rd column holding a ":".
| But when I paste into the text file, I have tabs separating the 3
| columsn where I was hoping for no space.
|
| So this here -
|
| NAQAHDAH 01 :
| NAQAHDAH 02 :
| NAQAHDAH 03 :
|
| should paste like this -
| NAQAHD6H01:
| NAQAHDAH02:
| NAQAHDAH03:
| NAQAHDAH04:
| NAQAHDAH05:
| NAQAHDAH06:
| NAQAHDAH07:
| NAQAHDAH08:
| NAQAHDAH09
|
| Is there perhaps a way to do this while keeping the 3 elements
| together in each cell of the column?  Or perhaps there's another way?
|
| Thanks. :oD
| 


0
nicolaus (2022)
4/7/2007 7:47:14 PM
Make that

="NAQAHDAH"&TEXT(ROW(),"00")&":"

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:eJrU$1UeHHA.4136@TK2MSFTNGP02.phx.gbl...
| If you start in Row 1, use this formula:
|
| ="NAQAHDAH"&TEXT(ROW(),"0000")&":"
|
| Fill down as far as you need
|
| Use ROW()-1 if you start in Row 2, etc
|
| -- 
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message news:kfrf1393vtsvqm2ffh4dtkc667ejlk75ao@4ax.com...
|| Is there a better way to create a "series" that is based on characters
|| as well as digits than what I'm doing below?
||
|| I created 3 columns.  I put the text in one, the digits in the second
|| that I could apply the series to, with the 3rd column holding a ":".
|| But when I paste into the text file, I have tabs separating the 3
|| columsn where I was hoping for no space.
||
|| So this here -
||
|| NAQAHDAH 01 :
|| NAQAHDAH 02 :
|| NAQAHDAH 03 :
||
|| should paste like this -
|| NAQAHD6H01:
|| NAQAHDAH02:
|| NAQAHDAH03:
|| NAQAHDAH04:
|| NAQAHDAH05:
|| NAQAHDAH06:
|| NAQAHDAH07:
|| NAQAHDAH08:
|| NAQAHDAH09
||
|| Is there perhaps a way to do this while keeping the 3 elements
|| together in each cell of the column?  Or perhaps there's another way?
||
|| Thanks. :oD
||
|
| 


0
nicolaus (2022)
4/7/2007 7:49:43 PM
On Sat, 7 Apr 2007 21:49:43 +0200, "Niek Otten" <nicolaus@xs4all.nl>
wrote:

>Make that
>
>="NAQAHDAH"&TEXT(ROW(),"00")&":"

That's just incredible.  No matter how many new things I learn in XL
each day, they never cease to amaze me.  This works beautifully.

Each time I'm adding multiple lines to the text file, I just need to
replace the preface text (in this case "naqadhdah") with whatever is
needed each time.

Also, by adding 2 spaces after the ":", I no longer have to fiddle but
can add the definition lines needed.

The formula for this particular entry looks like this now:

="NAQAHDAH"&TEXT(ROW(),"00")&":  "

Thanks.  :oD

>-- 
>Kind regards,
>
>Niek Otten
>Microsoft MVP - Excel
>
>
>"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:eJrU$1UeHHA.4136@TK2MSFTNGP02.phx.gbl...
>| If you start in Row 1, use this formula:
>|
>| ="NAQAHDAH"&TEXT(ROW(),"0000")&":"
>|
>| Fill down as far as you need
>|
>| Use ROW()-1 if you start in Row 2, etc
>|
>| -- 
>| Kind regards,
>|
>| Niek Otten
>| Microsoft MVP - Excel
>|
>| "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message news:kfrf1393vtsvqm2ffh4dtkc667ejlk75ao@4ax.com...
>|| Is there a better way to create a "series" that is based on characters
>|| as well as digits than what I'm doing below?
>||
>|| I created 3 columns.  I put the text in one, the digits in the second
>|| that I could apply the series to, with the 3rd column holding a ":".
>|| But when I paste into the text file, I have tabs separating the 3
>|| columsn where I was hoping for no space.
>||
>|| So this here -
>||
>|| NAQAHDAH 01 :
>|| NAQAHDAH 02 :
>|| NAQAHDAH 03 :
>||
>|| should paste like this -
>|| NAQAHD6H01:
>|| NAQAHDAH02:
>|| NAQAHDAH03:
>|| NAQAHDAH04:
>|| NAQAHDAH05:
>|| NAQAHDAH06:
>|| NAQAHDAH07:
>|| NAQAHDAH08:
>|| NAQAHDAH09
>||
>|| Is there perhaps a way to do this while keeping the 3 elements
>|| together in each cell of the column?  Or perhaps there's another way?
>||
>|| Thanks. :oD
>||
>|
>| 
>

0
StargateFan
4/7/2007 9:41:23 PM
Reply:

Similar Artilces:

Text in pivot table
I'm aware that summary functions will not work for text data in Excel 2000 (eg something like 'first' in Access crosstabs doesn't exist). Two questions: - Is this functionality available in later versions? - What are the alternatives (eg anyone got some VBA they've used to do it)? Thanks GB Pivottables are still quantitative summaries. You may want to give more info about what your data looks like and what you want. I might lead to better alternatives. green biro wrote: > > I'm aware that summary functions will not work for text data in Excel 2000 > (...

Text only emails through Internet
I have a remote user on Outlook 2003 who connects to my Exchange 5.5 server through the Internet. She is able to send and receive email fine. Her problem is when someone sends her an email with graphics in it, the graphics are stripped out. However, when she connects to the VPN, the emails come through with their graphics. Are there any settings I need to check on my end (in Exchange) or in her Outlook to make the emails appear like the sender wanted them too? Thanks, John "AnytimeAnywhere" <gm79ta@yahoo.com> wrote in message news:e7uQvRrnGHA.784@TK2MSFTNGP03.phx.gbl...

Moving data series horizontally (offset) to prevent overlapping
Hi, I could use some help with my Line with Markers -chart. My four different series overlap each other a little, which isn't that much of a problem until I add error bars, which are almost impossible to format so that you can know for sure which error bars belong to which data series. Is it possible to move a data series (including its error bars) right or left just a few pixels so the error bars are distinguishable from each other? I've seen this done in some published charts, but can't find a way to do it. Using google I found an example of what I need: http://www.emera...

Sending emails either as plain text or HTML
I am using Outlook 2003. With my contacts I would like to set the default send type. i.e contact A I would like to send to in plain text but contact B as HTML, if they appear in the same email it defaults the lowest level, i.e plain text. Most of my emails are in HTML but I have one contact that can't accept HTML so it keeps bouncing, I however don't want to change my setting and send everyone a plain text message I want outlook to send plan text message when that contact is being sent to. Is this possible, if so how? John Dean you can't define a format for contacts. Y...

how to hyperlink text to a cell
I am a new user of microsoft excel and being a health professional I need to use it for my research purposes. I have to put in a lot of text in each cell of first column for reference of each row.I would like the entire text in each cell to be on display when I take the cursor to each cell of the first column. How do I hyperlink the first two words of the visible text in each cell of first column to the remaining text typed into that particular cell,so that when I take the cursor to the first two words in the cell, the remaining text is displayed in the form of a drop down menu withou...

Is it possible to enter more than 255 characters in a cell
Is it possible to have more than 255 characters in a cell. I know that the limit is 1024 but I want to be able to see and print all of the characters. Thanks Dajana Dajana, I think it will work if you manually enter some line breaks in the text with ALT+Enter -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dajana" <Dajana@discussions.microsoft.com> wrote in message news:F7F6A0DF-0DA3-457B-A576-7814DBE7136D@micro...

Converting an Excel file to fixed length ASCII (text) file
I would like to convert an Excel table to a fixed length ASCII file for data transfer. Is this possible with Excel XP or an available download? File/Save As and select the type Formatted Text (space delimited) (.prn) Beware of the following caveats (from Help for XL 2002). I have no idea what they mean by "Columns of data are separated by commas". If these limitations affect you, you may need to write VBA code to create your text file. That would not be limited to 240-character lines. "The Formatted Text (*.prn) format saves only the text and values as they are displayed in...

Series colors
I cant find the syntax to specify the color of each series in a line chart. Can someone help? Thanks. I get as far asWorksheets("mysheet").Chart(1).SeriesCollection(n) ... Hi Dan, Try something like this, Worksheets("Sheet1").chartobjects(1) _ .chart.SeriesCollection(1).Border.ColorIndex = 4 Dan Sully wrote: > I cant find the syntax to specify the color of each series in a line chart. > Can someone help? Thanks. > > I get as far asWorksheets("mysheet").Chart(1).SeriesCollection(n) ... > > -- Cheers Andy http://www.andypope.info...

chart with two data series and two colors for each data series
Hi! I am using visual studio.net 2.0 and trying to use OWC11 to draw a chart from the database. If its only one data series it is fine but i cant see the second data series. all i was trying to do was display actual and budgeted against year. below are the code for clarification gvPortfolioHisex.DataSource = dtPropertyCapex gvPortfolioHisex.DataBind() 'Now Build a graph from the dataset Dim oChartSpace As New Owc11.ChartSpaceClass() Dim objChart As Owc11.ChChart = oChartSpace.Charts.Add(0) objChart.HasTitle = True objChart.Title.Caption = "Historical Expenditure Graph&quo...

Financial series vs Sales Series
This may or may not be an unusual question, but when I have to do something in GP 9.0 like a utility and it tells me that everyone 'has to be out of the system to do so', does this include anyone who is doing something 'strictly with the financials?' I had told my employer one day that he would have to get out of the system so I could take care of what he requested that I take care of and he said he wasn't doing anything with sales or inventory, just working on the 'financial side' and he shouldn't have to get out. Because of my instance that it would o...

Text labels on a Userform
For my userform, I am handling "UserForm_Click()" to detect when someone clicks on the form. The problem is, my Userform contains about 20 individual "text label" controls which sit on top of the form. Therefore, UserForm_Click() only gets called someone clicks an area that is not covered by a text label.... Is there an easy way to detect if someone clicks on ANY area of the userform?? I fear that I might have to handle 20 individual "Label_Click()" functions in addition to the UserforClick() function, which seems like overkill. thank u Hi, ...

Autofill a series from the name box?
If I want to autofill a series of values from 1 to 10, using the autofill handle is the easiest way to do it. But if I want to autofill 1 to 1000, that's a bit more tedious with the autofill handle. Is it possible to do it from the name box? I've been told it can be done but I can't figure it out. Help doesn't tell you and I've checked several books and references, but they all talk about using the fill handle only. You can use the name box to select the range you're interested in and fill a series. Suppose you want to fill A1:A10 with the sequential number...

automated email
Hi, Im wanting to know if its possible to create a function so my Outlook sends an automatic email to another email address, without prompting from me. I would like it to do this every 30 minutes. Is this possible? or are there any other solutions do to this? I use both Office 2000 and Office 2003 on seperate PCs so if its available under either that would be great, but how is it done?? If possible. not sure what else to add, but if you need to know extra information please let me know and I'll supply it to you. Any guidance would be superb thanks James ...

chart
Is there a way to graph mixed times series information (i.e. monthly data for 6 months that continues in biweekly increments) without coding involved? ...

Text box color code
Hi all, I am at loss as how to refer to a back color for a text box in Access 2007. I used to write Me.Textbox.Backcolor = 16777215 in earlier versions. In Access 2007 I don't know how due to the colors now having letters instead of numbers Can any one please help Regards and thanks in advance Hi JayTee, I had the same issue awhile ago. I found a little utility called "Color picker". You just choose your color, and it tells you what the color code is for HTML, VB and RGB. I can email it to you if you'd like to give it a try. JayTee wrote: >H...

How do I force a series in a Bar-Line Chart to be a Bar?
I have 4 series in a Bar+Line chart that has multiple Y-Axis and I want 3 of the series to reference the Primary Y-Axis using a Bar format and 1 of the series referencing the Secondary Y-Axis using a Line format. Excel seems to be forcing me to reference 2 series to one Y-Axis and the other 2 to the other Y-Axis. How can I force it to do what I want... I have been trying many settings and have not found the magical combination as of yet. Select the series. Select Chart Type from Chart menu. Select the type of series you want. In the future, you may find it easier to use a common chart...

If Then Text Question
I have a list of customer numbers and serial numbers. I need a formula that will find the customer number in a list, then fill the cell with the serial number (contains alpha and numeric characters, ie BBB1001). Example: If the value in cell A2 is equal to 1234, then find 1234 in a range (B2:B100), and retrive the serial number in a range (C2:C100). Thank you for your help. Hi use =VLOOKUP(A2,B2:C100,2,0) -- Regards Frank Kabel Frankfurt, Germany Debi wrote: > I have a list of customer numbers and serial numbers. I need a > formula that will find the customer number in a list, t...

Text Disappears from Controls after 4 Hours
Hi: I have a vc++ (vc.net 2003) dialog-based application that reads a 129 byte burst of data from the serial port every second and displays the received information every 5 seconds in a static text box, a couple of list boxes and a number of progress bars. The data is read in a separate thread and the thread posts a message to the main dialog on the 1st and 5th seconds. On the 1st second the static text box is changed and on the 5th second the list boxes and progress bars are updated. My problem is that after the application runs about 4 hours the static text box disappears. Then, if I mi...

We need consistency of layout from from series to series
Somehow the layout of pages from series to series is not consistent. For example in the Sales series the Transaction palette is in the upper left corner, Cards are on the upper right, Reports are in the bottom left. In the Inventory area I have the Cards in the upper left, Transactions are in the upper right and Inquiry is in the bottom left. How can I make it consistent from series to series? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the m...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

pop-up text boxes
I have composed an email newsletter using Publisher. When I sent myself the email to test the result I noticed a little pop-up text box appearing when the mouse hovers over any part of the page. It displays the first few words or lines of one of the text elements that I have included in the newsletter. It stays on screen for a few seconds then disappears until I scroll the screen down and hover the mouse again. The text in the box does not necessarily relate to the element the mouse is hovering over and therefore it's more of a nuisance than a benefit. What do I need to do at the de...

Can data in one text box automatically appear in another?
I am trying to design a form in Publisher, and would like to link text boxes so that data entered in one will automatically appear in another on the same page. Is this feasible? How will this form be used? "G.O.G." <G.O.G.@discussions.microsoft.com> wrote in message = news:E4C951DF-7D4F-4FC1-8F6B-2C0B4EB8882A@microsoft.com... > I am trying to design a form in Publisher, and would like to link text = boxes=20 > so that data entered in one will automatically appear in another on = the same=20 > page. Is this feasible? Maybe with some programming... -- Mary Saue...

How to suppress "(Autonumber)" text
I have a combo field that displays a number from an autonumber field. Before I create a record the text "(Autonumber)" is displayed in the combo field. Is there a way to suppress this so that the field is simply blank when no record has yet been created? Thanks. If you're running ACC2000 or later, use Conditional Formatting for the Combobox Condition1 Expression Is IsNull(YourFieldName) Now set the font/fore color to white -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via http://www.accessmonster.com ...

Text block query
I wish to create a block of several lines of text inside a border on a worksheet. What terms do I look for to describe this operation? Just wasted an hour looking in vain. I know I will kick myself when someone points me in the right direction. Thanks Brian Tozer Brian Tozer wrote: > I wish to create a block of several lines of text inside a border on a > worksheet. > What terms do I look for to describe this operation? > Just wasted an hour looking in vain. > I know I will kick myself when someone points me in the right > direction. Cancel panic. Problem solved. As usua...

Is it possible to print the contents of the Object Browser?
Call me a Neanderthal, but I'd like to have the object browser content on paper. Any suggestions on how to do this? When I click on an object class say, Application, I can see the list I want to print, but I can' select or print it. Any thoughts much appreciated! Gromi ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Buy this book http://www.wrox.com/books/0764543717.shtml This book have the entire Excel Object Model in it -- Regards Ron de Bruin (Win XP Pr...