VBA to add text character when number entered

CantGetRight

New Member
Joined
Jul 21, 2015
Messages
17
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for column "A"
Code:
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

New Member
Joined
Jul 21, 2015
Messages
17
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
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
[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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
Try this Worksheet_Change code. Assumes you are interested in column A only for this action.

Rich (BB code):
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

New Member
Joined
Jul 21, 2015
Messages
17
Thank you very much Peter. Exactly what I was looking for!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,002
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.
 
Last edited:

CantGetRight

New Member
Joined
Jul 21, 2015
Messages
17
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
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top