How to add a letter in front of number and behind a number

  • Thread starter Thread starter Rocky Lam
  • Start date Start date
R

Rocky Lam

I have a very large worksheet that in one of the column with is a product
number. Is there anyway that I can add a "Letter" in front of the product
number and the back of the product without going manual type each one. Here
is the example of a product number "12000989" and I like to do like this
"A12000989B". Of course each product number is unique in the column but the
letter is consistant throughtout the column.


Thanks for any help.

Rocky
 
You could put this in a module and run it:

Sub AddLetters()
For Each c In ActiveSheet.Range("D1:D1000").Cells
c.Value = "A" & c.Value & "B"
Next c
End Sub

Change the Range to the range your actually using and also the letters if
necessary.
 
Rocky

add a "helper" column. Assume that your product code is in column A and
starts in cell A2. Put the following formula in the second cell in the
helper column (for example, D2)

="A" & A2 & "B" and drag down on the fill handle (or double click if
there are no gaps)

Now copy the data in the helper column (from cell A2 down to the last row)
and use Edit | Paste Special | Values over the original data.

Regards

Trevor
 
One way:

In an empty column put this in the first row

="A" & A1 & "B"

copy down as far as required.

Copy the column and Paste Special/Values over the original column.
 
Even though these are product numbers, you could still very easily use a
"Custom" number format for the entire column, and convert all your data in
one fell swoop.

Select the column, then:
<Format> <Cells> <Number> tab,
Click on "Custom",
Then in the "Type" box enter:

"A"########"B"

Then <OK>
And you're done.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks for your help is working and help manually labor.
 
Back
Top