Solved

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

Posted on 2014-02-27
7
2,041 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 142

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select only certain columns not entire sheet 12 27
If Statement 3 19
VBA: insert new column and re-adapat string with lower letterS 4 26
Office 2016 without internet 6 36
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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

774 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