Adding a letter before existing data in a field

[u]

I have a simple spreadsheet that consists of a single column of Product
Numbers. It looks like this...


A
Product#
1 132ad
2 853dc
3 865xe
4 y85s4
5 pr8eb

etc... through about 10,500 more product numbers.

I want to add the capital letter "A" in front every single product number in
my spreadsheet. What is the most efficient manner of doing this? I tried
recording a macro, but all I could make it do was add the "A" to the first
product number, and then copy that exact product number to every other cell,
overwriting what was in there before. I ended up with 10,500 products all
with a product number of A132ad. Where am I going wrong?

 

[Norman Harker]

Hi u!

Save.
Save under a new name.
Add a helper column

Formula in Helper column:
="A"&A1
Copy down

Fastest way to copy down is to point to bottom left of the cell and
double click.

Now select the new column of Product numbers
Copy
Edit > Paste special > Values > OK

You can now delete (or hide) your old data if you want

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

You could use a formula and a help column

="A"$A1

copy down alongside and then paste special as values over the old ones

or you could run a macro

Sub Add_A()
Dim mycell As Range
Application.DisplayAlerts = False
For Each mycell In Selection.Cells
mycell.Value = "A" & mycell.Value
Next mycell
Application.DisplayAlerts = True
End Sub

press Alt + F11, click insert>module and paste in the above
press Alt + Q to close the VBE.

Back up the original data since there is no undo button for macros..
Select all cells with product numbers, press Alt + F8 to run the macro
called Add_A

 

[u]

ohhh! that did it! Thanks Norm!

 

[u]

ohhh! that did it! Thanks Norm!

 

[anon]

How about:

Sub Macro1()
Do While ActiveCell.Value > ""
ActiveCell.Value = "A" + ActiveCell.Value
ActiveCell.Offset(1).Activate
Loop
End Sub

 

[Gord Dibben]

And if you want a more generic Macro that will give you a choice of text to
add and where to add it........

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim whichside as Integer
Dim thisrng As Range
On Error GoTo Endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Else
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next
End If
Exit Sub
Endit:
MsgBox "Only Formulas in Selection"
End Sub

Gord Dibben Excel MVP