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
 

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
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top