VBA to add text character when number entered

[CantGetRight]

Hi all,

So this is a strange one and must be solved by using VBA. Any help is much appreciated.

Plain and simple, when a numerical character is entered into a cell I want the result to add "Group " before the number. So if someone enters 100, the result is "Group 100".

If the number has been pasted in, I still want it to amend it to add "Group " before it.

The only time I want it to not add this is when the cell content is anything but a number. If someone enters "123ABC" I want nothing to happen. So the entire cell must be numerical, cells that are text and numbers are fine and should not be changed.

Thanks in advance,

Mark

 

[MickG]

Try this for column "A"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    If IsNumeric(Target.Value) Then
        Target.Value = "Group " & Target.Value
    End If
End If
End Sub

 

[CantGetRight]

Hi Mick,

Thanks for your post. So this does work but only when typing it and hitting enter. If I past a range of numbers into rows in Col A this does not work. Is there a workaround?

Thanks,
Mark

 

[CantGetRight]

Mick is this something you can help me with or should I open a new ticket? Thanks

 

[MickG]

Perhaps this ??:-
If you copy and paste a range of cells, if you then run this code just after you paste the selection (ensuring the pasted range is still selected) then it should do the same job.
You can do the same on any selected cell or selected range.

[COLOR="Navy"]Sub[/COLOR] MG06Feb53
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Selection
[COLOR="Navy"]If[/COLOR] IsNumeric(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Value = "Group " & Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

Try this Worksheet_Change code. Assumes you are interested in column A only for this action.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, cell As Range
  
  Set Changed = Intersect(Target, Columns("A"))
  If Not Changed Is Nothing Then
    For Each cell In Changed
      If IsNumeric(cell.Value) And Not IsEmpty(cell) Then cell.Value = "Group " & cell.Value
    Next cell
  End If
End Sub

 

[CantGetRight]

Thank you very much Peter. Exactly what I was looking for!

 

[Peter_SSs]

Thank you very much Peter. Exactly what I was looking for!

You're welcome.

 

[Eric W]

I know you said that you wanted a VBA solution, and you have a satisfactory solution from Peter. But I think it's worth noting that you can achieve the same effect with a custom format. Format column A, select Custom format and enter

"Group "0;-0;0

and positive numbers will show as "Group 1" (but still just equal the number value in the cell, making formulas easier), negative numbers, zeros, and text show as themselves. Positive non-integers will be rounded.

 

[CantGetRight]

Hi Eric,

Thank you, yes this also works but not when pasting into this range. Unless of course you paste values. Peter's suggestion is the desired result for my purpose.

Thanks,
Mark