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

Jagwarman
Jagwarman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

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

Author

Commented:
Both great solutions. EE forces me to say one was 'Best Solution' but they are both equally as good. Thanks
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

You're very welcome :)

Author

Commented:
Guy a question for you......

If my sheet is not called Sheet1 how do I change this to make it run?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
Presumably activesheet  will do the job

Author

Commented:
Thanks Guy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial