Solved

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

Posted on 2014-02-27
7
2,082 Views
Last Modified: 2014-02-27
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
Comment
Question by:Jagwarman
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39891477
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
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
ID: 39891480
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
 

Author Closing Comment

by:Jagwarman
ID: 39891497
Both great solutions. EE forces me to say one was 'Best Solution' but they are both equally as good. Thanks
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39891511
You're very welcome :)
0
 

Author Comment

by:Jagwarman
ID: 39891535
Guy a question for you......

If my sheet is not called Sheet1 how do I change this to make it run?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39891547
Presumably activesheet  will do the job
0
 

Author Comment

by:Jagwarman
ID: 39891556
Thanks Guy
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question