Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Word and change format
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format fonts (ie, initial cap) in Excel like you can in Word. | Excel Discussion (Misc queries) | |||
Moving Excel Charts into Word Documents: font sizes change | Charts and Charting in Excel | |||
Conditional format (word change) | Excel Worksheet Functions | |||
Embedded word doc changed to image-need to change back to word. | Excel Discussion (Misc queries) | |||
Excel format changes as I merge into Word document How can I stop | Excel Discussion (Misc queries) |