Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2570
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
[ 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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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