How to prepend a string to many cells? 1

[cpjust]

Hi,
I can probably figure out how to do this using VBA, but I was wondering if there is any easier way to do it (maybe a built-in function) in Excel.

I have a column containing hundreds of Test ID numbers like this:
1.1.1.1
1.1.1.2
1.1.2.1
...

What I'd like to do is highlight some of them and add "a." infront of the existing numbers to become:
a.1.1.1.1
a.1.1.1.2
a.1.1.2.1
...

Is there an easier way to do that without writing a VBA macro?

 

[AnotherHiggins]

There is no built-in function to prepend the text in a cell. You could use a function in another column to concatenate your text, though.

[tab]=concatenate("a",A2)
or, more simply,
[tab]="a" & A2

a quick and dirty macro would look something like this:

For each cell in Selection
[tab]cell.value = "a" & cell.value
Next cell

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

What do you want to do this for? If it's just presentational you could use a Custom Format - select the cells and format them as [blue][tt]\a\.@[/tt][/blue] - but do note that this will not change the actual value so may not be any use to you.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[xlbo]

If this is for presentation only, you could use a custom format of:

"a."@

the data will remain the same but will show as you have asked

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cpjust]

Thanks! That's exactly what I needed. For some reason I thought it would be messier than that.

Sub PrependToCells()
    Dim preText As String
    preText = Application.InputBox("Enter text to prepend:", "Enter text to prepend")

    'If user clicks Cancel, exit.
    If preText = "False" Then
        Exit Sub
    End If

    'Otherwise, update all highlighted cells.
    For Each cell In Selection
        cell.Value = preText & cell.Value
    Next cell
End Sub