CODEWITHSUNDEEP
excelvbaexcel-formula
Thomas Burleigh
Thomas Burleigh

Reputation: 33

Bulk editing cells in Microsoft Excel

I need to edit a large number of cells in Microsoft Excel and I have no idea what the best way to accomplish what I want is.

Basically, I have a list of thousands of ids like this:

503kekd23k0dLY4_
ytp-4r30=-340rR_
HdgfkER4ww=_4--3
=4tR_Ee4we403Prr

I need to be able to add a prefix of "UC_" to each of these cells so the cells look like:

UC_503kekd23k0dLY4_
UC_ytp-4r30=-340rR_
UC_HdgfkER4ww=_4--3
UC_=4tR_Ee4we403Prr

I need to run a Macro of some form in order to accomplish this most likely, but I'm not sure how to do this. If you can help, let me know.

Upvotes: 3

Views: 15923

Answers (4)

Dan Donoghue
Dan Donoghue

Reputation: 6216

If you "want" to do this with VBA, you can do it with one line of code:

Range("C6:C9").Formula = application.transpose(split("UC_" & join(application.transpose(range("A6:A9")),"|UC_"),"|"))

This posts the results to column C but you can post direct to Column A if you wish. I used A6:A9 for my testing, make sure you update both ranges.

The theory is join the strings using |UC_ as the delimiter join(application.transpose(range("A6:A9")),"|UC_"). I added the pipe char | so I can easily break it apart later, if you have a pipe in your data change the pipe to a none used char (look through the ascii table there are plenty of none showing chars you can use or in this case you could even use vblf). Once joined then split it apart using the pipe (or whatever char you choose) and plop back into a range.

You need to add |UC_ to the start as join will not put one at the start, I have done that here: "UC_" & join(applic........

We use application.transpose to change the array to something that can be populated to a range, an array typically goes across, you want it to go down. Note if you are playing around with this in the future, to go across columns you actually need to double transpose.

Upvotes: 0

PermaNoob
PermaNoob

Reputation: 869

If your id is in cell A1, you could use this formula in cell B1

="UC_"&A1

Then drag that formula down.

The "&" symbol basically acts like a plus sign for text. You put the UC_ in double quotes to denote it's a string, then just "add" it to the id.

Upvotes: 4

teylyn
teylyn

Reputation: 35925

In Excel 2013 and above, you can use Flash Fill.

Manually enter the first value. Select that cell and click Flash Fill on the Data ribbon.

Before

Before

After

after

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96763

You can do this manually with a "helper" column or select the cells and run this:

Sub AddPrefix()
    Dim r As Range
    For Each r In Selection
        r.Value = "UC_" & r.Value
    Next r
End Sub

Upvotes: 3

Related Questions