<

Add letter before content in a cell (excel macro)

Published on
12,288 Points
6,288 Views
Last Modified:
Approved
I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document.

My English is as it is, so I will try explain what it does diffrently.

If you have an excel document with 2000 rows and you need to add an "A" before every row under column "A", this is what you can do.

First you'll need to create a new macro in excel, when you do the "Microsoft Visual Basic Editor" will open.

In the editor, paste this code (also included below):
--------------------------------------
Sub AddLetterMacro()

For x = 1 To 2000

    ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text
   
    ActiveCell.Offset(1, 0).Select

Next x
End Sub
--------------------------------------

At this row:

For x = 1 To 2000

change the value "2000" to the number of cells in your document.

then go to:

ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text

and change "THE LETTER" to any letter you want to be placed before every cell in that row.

Now go to your Excel document and click in the cell where your should start, then go to the editor again and press the "Play(>)" button or just press "F5" and the macro will go off.

This macro could probably be done much better to count the rows in the document etc. but it's an easy way to go.

You could also add an letter to the end of the cell instead of the beginning by simply changing:

ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text

to

ActiveCell.FormulaR1C1 = ActiveCell.Text + "THE LETTER"

Hope this article will be to some use for you.

And also, this macro has been tested on both MS Excel 2003 and MS Excel 2007.

// Tobias
0
Comment
Author:Gudorian
4 Comments

Expert Comment

by:Mariianna
thnks for this article
i have to try
:)
0
LVL 3

Expert Comment

by:jack_
Yes it is helpful.Thanks for the article

jack
0
LVL 21

Expert Comment

by:alainbryden
This seems to be a bit small for an article. The task itself is fairly simple and does not have a wide variety of application. It's for a very specific need and there are many ways of solving it with and without macros, including more efficient and more generic ways.

Furthermore, what the actual code is doing is taking the current displayed text in the active cell, and writing it into the formula of the active cell. These are very specific commands and will be broken by considerations such as whether the desired sheet is actually active at the time. Also, if the user clicks somewhere in the sheet while the program is running, it will break the formula and start adding the letter to cells below the one that the user clicked.
0
LVL 43

Expert Comment

by:Saqib Husain, Syed
I agree that this article cannot qualify as the information is very basic and may be found on the net in some form or the other. Also as alainbryden has stated there would be many ways of solving it.

The author might cconsider looking into a variety of ways to accomplish this task and publish them together with their pros and cons.

Saqib
0

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Join & Write a Comment

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month