• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3039
  • Last Modified:

vba to insert character in front of every string of numbers column 'A'

I need to an x to every every string of numbers in column A starting at A2

so if cell A2 = 012345678 I need to add an x so that it becomes x012345678

The number of rows will vary every day and I would like to use VBA please.

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 3
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do
 Public Sub x()
  Dim r As Range
  Dim c As Range
  
  Set r = Sheet1.Range("A2:A" & Sheet1.UsedRange.Rows.Count)
  For Each c In r.Cells
    If Not (c.Value2 Like "x*") Then
      c.Value2 = "x" & c.Value2
    End If
  Next
  
End Sub

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

...and this code checks if the value is actually numeric, rather than not being prefixed with an "x":

Option Explicit
Public Sub Q_28375881()

  Dim objCell                                           As Range
  
  Worksheets("Q_28375881").Select
  
  For Each objCell In Range([A2], Cells(Cells.Rows.Count, 1).End(xlUp))
  
      If IsNumeric(objCell) Then
         objCell = "x" & objCell.Text
      End If ' If IsNumeric(objCell) Then
  
  Next objCell
  
  Set objCell = Nothing
  
End Sub

Open in new window


Please also see the attached workbook.

BFN,

fp.

PS. There will be a finite limit to the number of characters than the IsNumeric() function can handle.

If you could advise what the maximum number of characters will be in each of the cells in column [A] (row 2 onwards), then the code above may need to change to test the numeric value of each character instead.  (This could be achieved using Regular Expression syntax for speed).
Q-28375881.xls
0
 
JagwarmanAuthor Commented:
Both great solutions. EE forces me to say one was 'Best Solution' but they are both equally as good. Thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome :)
0
 
JagwarmanAuthor Commented:
Guy a question for you......

If my sheet is not called Sheet1 how do I change this to make it run?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Presumably activesheet  will do the job
0
 
JagwarmanAuthor Commented:
Thanks Guy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now