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

ID	Items
1	Orange
1	Bread
2	Milk
2	Bread
2	Apple
3	Knife
3	Plier

<tbody>
</tbody>

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:

ID	Items
101	Orange
101	Bread
102	Milk
102	Bread
102	Apple
103	Knife
103	Plier

<tbody>
</tbody>

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.