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

#### sandspider

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### MichaelRo

##### Well-known Member
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``````

#### sandspider

##### New Member
Many thanks.

A bit of modification, and sorted!

Cheers,

Giles

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,128
Messages
5,857,529
Members
431,883
Latest member
Hien

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back