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

[sandspider]

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

 

[MichaelRo]

For Q1:

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:

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]

Many thanks.

A bit of modification, and sorted!

Cheers,

Giles