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?

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?

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.