Enclosing data in " " via macro, plus adding a 3 t

sandspider

New Member
Joined
Oct 10, 2005
Messages
23
Hi all.

1) I have a number of columns of data in a .csv file. I need to enclose the contents of one of these columns (variable length text) in double apostrophes ("text text text").

2) Within the same document, I also need to put a single digit, 3, on the start of a column of numbers. The numbers are all 7 digits long. i.e.

0002543
0104679
...

need to become

30002543
30104679
3...

Please could anyone suggest how to do both of these things via macro?

Thanks very much,

Giles
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
For Q1:

Code:
Sub EncloseInSpeechMarks()

Dim irow As Long
Dim txt As String

'Assumes Your Data is in Column A Starting at row 2
'Change as appropriate
'Loops Through Column A
For irow = 2 To Range("A65536").End(xlUp).Row
    'Defines Cell Value as Text Value
    txt = Range("A" & irow)
    'Add Speech Marks and gives cell new value
    Range("A" & irow) = """" & txt & """"
Next

End Sub

For Q2:

For you to have leading Zero's in a cell requires the cell to be formatted at Text - The following Code Add's the 3 at the beginning and changes the cell format to number:

Code:
Sub ChangeNumber()

Dim irow As Long
Dim val As Long

'Assumes Your Data is in Column B Starting at row 2
'Change as appropriate
'Loops through Column B
For irow = 2 To Range("B65536").End(xlUp).Row
    'Defines Cell Value as Number Variable and Adds 3 at the beginning
    val = "3" & Range("B" & irow)
    'Formats Cell as Number Format
    Range("B" & irow).NumberFormat = "0"
    'Gives Cell New Value
    Range("B" & irow).Value = val
    
Next

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,016
Messages
5,569,628
Members
412,284
Latest member
Daibear
Top