Add Word and change format

1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now 
I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the 
faster way in case I got thousand of codes?

2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc. 
Now I would like to make it to be standard to 4 digit for the middle number 
to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above, 
I got more than thousand of such codes... What's the faster way?
0
Kelvin (49)
11/24/2005 5:15:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
410 Views

Similar Articles

[PageSpeed] 11

Kelvin

The first could be done with a formula in a helper cell i.e ="Z" & A1 but as 
I find the second easier in VB both are included they will change the 
selection so you might like to copy the data and work with that

Copy the files into a vb Module, Alt & F11, Insert Module.
Return to the worksheet, select the data and Choose Tools, Macros and click 
on the appropriate code.

Sub addZ()
' Add 'Z' to each code
  For Each c In Selection
    c.Value = "Z" & c
  Next
End Sub

Sub ChangeCode()
' increase the mid section to 4 characters
' it changes mid sections with 3 or 3 characters
Dim l As Integer, i As Integer
Dim j As Integer, ln As Integer
Dim ch As String, chr As String
ch = "-"
  For Each c In Selection
     i = Application.Find("-", c) + 1
     j = Application.Find("-", c, i + 1)
     ln = j - i
     chr = Mid(c, i, ln)
     If Len(chr) = 2 Then
      chr = "00" & chr
      c.Value = Left(c, i - 1) & chr & Right(c, Len(c) - (j - 1))
        ElseIf (Len(chr)) = 3 Then
          chr = "0" & chr
          c.Value = Left(c, i - 1) & chr & Right(c, Len(c) - (j - 1))
     End If
  Next
End Sub

Regards
Peter


"Kelvin" wrote:

> 1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now 
> I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the 
> faster way in case I got thousand of codes?
> 
> 2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc. 
> Now I would like to make it to be standard to 4 digit for the middle number 
> to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above, 
> I got more than thousand of such codes... What's the faster way?
0
11/24/2005 10:56:02 PM
> 1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8",
> etc...Now I want add a "Z" in front the codes. To be "ZPK0021,
> ZUQ05P8". What's the faster way in case I got thousand of codes?

Here's one way.

a. Insert an empty column between A and B.

b. In the new B1, put 
       ="Z"&A1
   and copy down for as many rows as needed.

c. Select column B. 

d. Edit >> Copy

e. Select column A

f. Edit >> Paste special >> Values >> OK

g. Delete column B



> 2) In my column B is such code as "18-521-65, 18-81-84, 18-1112-65"
> and etc. Now I would like to make it to be standard to 4 digit for the
> middle number to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the
> same scenario as above, I got more than thousand of such codes...
> What's the faster way? 

Here's one way.

a. Insert three empty columns between B and C.

b. Select column B and use
      Data >> Text to columns >> Delimited
   choosing the hyphen character as the delimiter.

c. In E1 put
    =B1&"-"&TEXT(C1, "0000")&"-"&D1
   and copy down for as many rows as needed.

d. Select column E. 

e. Edit >> Copy

f. Select column B

g. Edit >> Paste special >> Values >> OK

h. Delete columns C, D, E.

0
11/24/2005 11:02:57 PM
Reply:

Similar Artilces:

How do I add a newsgroup account to WLM?
I am trying to add a Newsgroup account to WLM. I am at the point where you enter an NNTP server. I don't know how to configure this. Please help so I can leave you alone. Thanks in advance. Not knowing what NGs you're trying to add . . . . . you can put "msnews.microsoft.com" in there, and on the Advanced tab, enter 119 as the port - that should get you to the Microsoft Communities newsgroups. "sheana" <sheana@discussions.microsoft.com> wrote in message news:15CC44DC-56E5-4560-9FD0-50EBBEAD2F14@microsoft.com... > I am trying to add a Newsg...

PDF Add-in for Excel 2003
Hi, Excel 2007 has a feature which enables you to create a PDF document from the spreadsheet. Is there an add-in available for Excel 2003 that does the same thing? I am aware of PDF writer applications which work by sending the printed output to piece of software, but this is not what i'm looking for. So please do not reply, suggesting software similar to Cute PDF, PDF 995 or PDF Redirect. These programs do not give us the desired output, so I am only interested in add-ons which are similar to the 2007 feature. Chris. Thu, 21 Feb 2008 02:38:00 -0800 from Kentucky <Kentucky@di...

Duration getting changed automatically.
Hi... I have tasks with task type as fixed duration. While importing data for assignments for that task I notice that the DURATION gets AUTOMATICALLY changed in 2 cases:- 1) When I am importing actual effort for assignment or 2) When I import actual start date for that assignment( assignment doesn't have actual finish date as of now). I am not able to find the logic why MSP is doing that. Any help or insight into the behavior is really appreciated!! Thanks a lot in advance!!! -- --Sumit -- --Sumit Are you IMPORTING the data or manually inputting it? If...

How to change attributes in an xml-file
Hi, I wrote a small c# programm, which generates an xml-string by serializing a class and sends it to a server. Now I want to change some values in that xml-string. The only way for me to do that might be, to deserialize it, change the particular attribute and serialize it again, to finally save it on my harddisk. But I believe, that there is a more convenient way to do this, since it is also possible to append an element... Can anybody help me in this matter? What about the class XmlAttribute? I appreciate for you help. Cheers Philipp Philipp wrote: > I wrote a small c# pro...

Format Chart based on data
I have built a chart from a simple spreadsheet of data. Here is an example of the data: Group1 13.77 Group2 15.86 Group3 12.11 I want the chart (bar style) to show each bar in Blue that corresponds to a value greater than 13. All other bars (<13) should be red. Is that possible? Also, I want to draw a line across the chart at 13 to show the "benchmark". http://peltiertech.com/Excel/Charts/ConditionalChart1.html http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTe...

Text color change in a cell depend on the cursor position elsewher
Excel. I want either the background color or the text color of a cell, or cells, to change when the cursor is in a particular position. e.g. The background/text color change for the far left cell of a row when the cursor is anywhere in that row. Hi! Two things: 1. You can get a cell to change colour with a VBA routine such as: Private Sub Worksheet_SelectionChange(ByVal Target As Range) r = Target.Row Cells(r, 1).Interior.ColorIndex = 6 End Sub This paints the first cell in the row yellow. But you'll need to give some thought as to whether/how you will dea with moving off that ro...

Prevent changing PO number sequence from PO Entry screen
In Purchase Order Entry, if a user enters a PO number which does not exist as an open or historical PO and which is lower that the current Next Number in Purchase Order Processing Setup, the Next Number is changed to the lower number even if the user deletes the PO without saving. Obviously, this wrecks havoc with PO number sequence. The system should not allow the Next Number sequence to be changed from the PO Entry screen. Ideally, there would also be an option to prevent the user from overriding the next PO # similar to what is available in SOP document setup. ---------------- Th...

Places COM Add-in for Excel XP?
Ive installed the addin for excel 2000 on my version of excel 2002 (xp) and it doesn't work. Does anyone know if this addin is available for Excel 2002? Or of another addin which does the same thing? Thanks Adam With Excel 2002 you don't need the Places add-in. You can add folders via the Tools drop down in the Open dialog. And you can remove items by the popup menu when you right-click them. You can also change to "small icons" with the right-click menu. -- Jim Rech Excel MVP ...

Change of title bar
Hey guys… Has anyone out there been able to change the blue title bar (right below the toolbar) in form using code? We need to display the name of the account and the account number in the title bar. We can’t use the name and number in the same field. They have to be saved in different fields, but they have to be displayed as one in the bar. Anyone, anyone? Try: var oTBody = document.body.firstChild.firstChild.nextSibling; var oTable = oTBody.firstChild.firstChild.childNodes[4]; oTable.firstChild.firstChild.firstChild.innerText="My New Title"; HTH, R. "Guillermo Car...

Lost formatting in e-mails composed in Outlook
When I compose e-mails using Outlook--either in Word or not in Word--the recipient receives the e-mail with all formatting stripped from it. I can't use color, different fonts, italics, etc., because all disappear between "Send" and "Receive" even when I send it to myself. I finally found and chose the option of NOT removing formatting when sending, no matter what program it is sent to, but it removes all formatting anyway. Anybody know how to make it leave all the formatting in e-mails, either in Word or not in Word, when sent? Also, anybody know how to turn off AL...

How to add company name in activitylist of home page?
Thanks Chris Yes can create a pre-pend to activity subject, that when a activity is created, it will pre-pend the activity subject with the contact or account name. therefore, when going to the home page all activities will start with "ABC Company - call Bob about deal " etc, etc Hi Mike Could you be a bit more specific? How can i do this? Thanks! Christian Mike wrote: > Yes can create a pre-pend to activity subject, that when a activity is > created, it will pre-pend the activity subject with the contact or > account name. therefore, when going to the home page al...

formating issue
Hello, I use excel 07. I have an excel sheet. On the sheet I have tables spread vertically and not horizontally. That is, I have three tables placed beneath each other. Now, If i play around with the width of a column, of course the width of the cells on the same column chnage accordingly. This is what i dont want it to happen. I want to arrange cells widths independent from each other, while at the same time the keep other tables' cell widths fixed. Now you will suggest to put tables horizontally and change widths then. The problem is that I copy and paste the tables to word. in the...

Indent formatted cell
I would like cells that are formatted with comma (no decimals) to be indented. I can do it to non-formatted cells, but once I apply the currency or comma style, I can no longer indent the cells. I wish to do this to line them up closer to the centre and thus under the heading at the top. Thank you Deborah Hi, Deborah- You don't specify what your comma format cells look like so I don't know if this will work. How about if you leave the cell unformatted, and achieve the comma formatting with the TEXT() function. Then you can apply the indent to the cell. Hope this does it for you...

changing platforms from PC to mac
can I buy the upgrade version of MS Office for a mac since I bought the full version of MS Office 2000 for a PC two years ago? How do I go about doing it? The Microsoft page has all of the details on upgrade requirements: <http://www.microsoft.com/mac/howtobuy.aspx?pid=upgrade> The page indicates that as the owner of a previous version of Office for Windows, you are entitled to upgrade pricing on Office 2001 (the OS 8/9 version), but not Office X (the OS X version). You could buy the upgrade version of Office 2001 and then purchase the upgrade version to Office X if you desire the ...

Six conditional formatting possible?
Is there any alternative way to create six or more conditional formatting and how can I save my formats? User is on self study and would appreciate step by step method on how to create if there's any. Thanks As an example, tinker with this sheet sub* which conditionally colors (both fill colors and font colors) in cols A to T (20 cols) based on the numeric values -- between 1-30, inclusive -- in col U (within U2:U100) *from a Mike H posting To install: Copy the sub Right-click on the sheet tab > View Code Paste the sub into the code window on the right Press Alt + Q to get back to...

add controls on the fly/dynamically
Hi All. I use this code to rename the tabs of multipage control on a userform. Sub page() For Each Cell In Sheets("Data").Range("major_streams") frm_daily_entry.MultiPage1.Pages.Add Cell.Text Next End Sub The problem I have is that when creating the tabs on the fly I als have to create other controls on the fly too, specifically Labels textboxes. The Labels & Textboxes will be defined by looking up the caption of th current tab (on sheet1) and adding however many label/Textboxes tha are required (one for every item under the heading on the sheet). Anyone have...

Change the color of Filter Arrows in Excel to BRIGHT RED
I suggest that Miscrosoft changes the color of the Filter Drop Down List Arrows to Bright Red, when the column is filtered. Trying to distiguish the non-filtered columns (black arrows) from the filtered columns (currently dark blue) is almost impossible. Make this drop down arrow Bright Red would make it easier and obvious that the spreadsheet you are viewing is limited by a filtering critieria. if you'ld like to tell MS about your ideas you'll need to email them at mswish@microsoft.com with excel as the subject line and your ideas as the message body, this is a peer based sup...

how to add times from decimal to time?
need to find out fow to addd time that is in decimal format and desplay the answer in time for example 1.5+1.5= 2min & 30sec Hi one way: =A1/24+A2/24 and format the resulting cell as 'Time' opr the custom format [hh]:mm "aidomatic" wrote: > need to find out fow to addd time that is in decimal format and desplay the > answer in time for example > 1.5+1.5= 2min & 30sec In Excel, times are decimal fractions of a day that are simply formatted to look like time. Thus =2.5/24/60 when formatted as time displays as 0:02:30 (2 minutes and 30 seconds). Howeve...

IE8 Add to Favorites
In IE8, when I want to add a site to My Favorites, when I go to select the folder to save it in, the list of all of my previously saves favorite folders completely opens up in expanded view. Since there are folders and multiple subfolders, is there a way to make only the half dozen or so main folders show up, then I could drill down and open each subfolder one at a time, instead of immediately seeing every single favorites folder I have ever created in a VERY long scrolling list? "M Skabialka" <mskabialka@NOSPAMdrc.com> wrote in message news:#nZS5pN$KHA....

html address with word art
Hi, I have a word based off word art as a hyperlink to a website. What I would like to have happen is The word pops up(gets bigger) when hoverd on and then when clicked turn a different color. Can I do this with word art? It will be published on the web as adobe acrobat file. Help new to publisher. What you want is a *rollover* A graphic element in an application or on a Web page that changes its color or shape when the pointer is moved (rolled) over it. Look here http://www.google.com/search?q=mouseover&revid=1110667976&sa=X&oi=revisions_inline&resnum=0&ct=broad-...

Can someone explain the styles term 'theme-aware' in Word 2007?
I keep reading about theme-aware formatting in conjunction with styles and themes. Can someone please enlighten me. On Sat, 3 Apr 2010 12:33:40 -0500, "Robert" <instructor@hotmail.com> wrote: >I keep reading about theme-aware formatting in conjunction with styles and >themes. Can someone please enlighten me. If you look at the definition of a style and it says that the font name is "+Body" or "+Headings", and the color is "Text 1" (or another number) or "Accent 1" (or another number), then the style is theme-awar...

Change Associated Public Folder tree
Hi, I am unable to change Associated Public Folder tree after i created the PF store?!! When i go to Public Folder properties, there is no Browse button there to allow me to associate existing PF store to another tree. Do i need to use ADSI edit to perform this simple thing? pls guide. On Thu, 8 Jun 2006 08:03:02 -0700, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >I am unable to change Associated Public Folder tree after i created the PF >store?!! >When i go to Public Folder properties, there is no Browse button there to >allow me to associ...

32 bit format
I'm blocked from existing publisher documents on my computer. box comes up saying it's not a valid 32 bit format??? This had not been a problem in the past?? any help is appreciated After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Jim <anonymous@discussions.microsoft.com>... > I'm blocked from existing publisher documents on my > computer. box comes up saying it's not a valid 32 bit > format??? Can you open Publisher OK? What version of Windows/Publisher are you running? Are you running a firewall/AV software? Have you chkdsk/scan...

My Excel shortcut icon has changed
As of today, my desktop shortcut icon for Excel has changed from the default icon to a cats face. I deleted it and recreated the icon but the cats face icon appears each time. Checked the virus websites and scanned my pc, but cannot find any answers, anyone help? The Excel icon in START>PROGRAMS is fine, its just if you create a new shortcut. Rakesh, Open Windows Explorer (Not Internet Explorer) and choose Tools | Folder Options.... Select the "File Types" tab, then scroll down to find "Microsoft Excel Worksheet." Click "Edit..." and then click &qu...

Change Reports List View
Hello, Does anyone know if it is possible to change the reports list view? For instance, I would like to eliminate the “Report Type” and “Modified On” columns and only keep the “Report Name” and “Description” columns. Thanks! ...