Help With Excel

[BobH]

I have a list of Address Book entries that I have extracted and placed in an excel spreadsheet. Some of these entries do not have email addys associated with them. I need to extract nicknames from a column for all entries that do not have email addys AND I need to transform the nicknames so that they are enclosed in double quotes and end with a comma. I have sorted the spreadsheet on the email addy column which separates out the entries and places those without addys at the bottom of the spreadsheet. So, I can select these cells and copy them, but how do I put the special characters in the cells I copy them to? Is there a function I can use to do this?

I need to send the same message to all recipients. I've already sent emails to those whose addresses I have. I'm using a forum to send PMs to those who don't have email addys. The forum will use the nicknames and I can use multiples but each nickname has to be contained in quotes and followed by a comma. There are several hundred names in the list. I will never type them in correctly if I try to do this without using automation to format the nicknames as required.

TIA

[Don Wells]

The following code assumes that the Nickname is in the 7th column and the Address is in the 4th column, The two constants will need to be adjusted to match your particulars,

H.T.H.

Code: Select all

Option Explicit
Public Sub AddNicknames()
Const NC = 7 ' Nickname Column (G)
Const AC = 4 ' Address Column (D)
Dim BR As Long ' Bottom Row
Dim Ctr As Long ' Counter

  BR = Cells(Rows.Count, 7).End(xlUp).Row
  
  For Ctr = BR To 1 Step -1
    If Cells(Ctr, AC) = "" Then
      Cells(Ctr, AC) = """" & Cells(Ctr, NC) & ""","
    End If
  Next Ctr

End Sub

[agibsonsw]

If a formula is preferred then =CHAR(34)&A1&CHAR(34)&CHAR(44)
will change Bob in A1 to "Bob",

[BobH]

Thanks Folks!

Because I wasn't sure how to invoke the code, I went with the =CHAR function. Worked a treat.

Thanks again.

Bob

[mbarron]

If you don't want to have to remember what the CHAR value for quotes(") or a comma (,) , you can use:
="""" & A1 & ""","

Double quotes "" within a set of quotes, results in a single quote output for a formula

[BobH]

Thank you for the information. I can remember learning a lot of functions of Visicalc back in '80 or '81, but it's truly amazing how much one can forget as one ages.