Solved

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

Posted on 2014-02-27
2,168 Views
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
Question by:Jagwarman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 143

Accepted Solution

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
``````
0

LVL 35

Assisted Solution

[ 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
``````

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

ID: 39891497
Both great solutions. EE forces me to say one was 'Best Solution' but they are both equally as good. Thanks
0

LVL 35

Expert Comment

ID: 39891511
You're very welcome :)
0

Author Comment

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

ID: 39891547
Presumably activesheet  will do the job
0

Author Comment

ID: 39891556
Thanks Guy
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month3 days, 19 hours left to enroll