Inserting a text as a "prefix" to multiple cells with different values

[fosiza]

Helo good people of mrexcel forum,

Lets say I have table like this
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Items[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bread[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Milk[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bread[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Knife[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Plier[/TD]
[/TR]
</tbody>[/TABLE]

I want to add a "prefix" (dont know if I use the correct term here) to every of the ID numbers. Lets say I want to add prefix "10". So the transformed table would look like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Items[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Bread[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Milk[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Bread[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Knife[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Plier[/TD]
[/TR]
</tbody>[/TABLE]

How can I do this?

EDIT: I mean of course, aside from manually typing "10" to every cell lol

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
Dim prefix As String
prefix = InputBox("Enter prefix")
If prefix = "" Then Exit Sub
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        .Value = prefix & .Value
    End With
Next i
End Sub

 

[Confused_At_work]

in a spare column you could have ="10"&A2 (assuming ID column is A), copy this down and then pastespecial

 

[kcube17]

="10"&cell reference

 

[Peter_SSs]

.. or applying to the whole range at once and skipping any empty cells

Sub AddPrefix()
  Dim sAddr As String
  
  sAddr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(sAddr).Value = Evaluate("IF(LEN(" & sAddr & "),10 & " & sAddr & ","""")")
End Sub

 

[fosiza]

Try

Sub test()
Dim LR As Long, i As Long
Dim prefix As String
prefix = InputBox("Enter prefix")
If prefix = "" Then Exit Sub
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        .Value = prefix & .Value
    End With
Next i
End Sub

Brilliant! Thank you my good man!

in a spare column you could have ="10"&A2 (assuming ID column is A), copy this down and then pastespecial

="10"&cell reference

Thanks for the alternatives guys!

 

[fosiza]

.. or applying to the whole range at once and skipping any empty cells

Sub AddPrefix()
  Dim sAddr As String
  
  sAddr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(sAddr).Value = Evaluate("IF(LEN(" & sAddr & "),10 & " & sAddr & ","""")")
End Sub

Also a brilliant solution! Thank you Peter.

Alright guys, I think Ive learned enough today, thank you all for the solutions.

 

[Peter_SSs]

Lots of choice.
Thanks for the feedback.

 

[hiker95]

Peter_SSs,

Another Evaluate solution to add to my archives - thank you so much.