1

I currently have a spreadsheet with the following format 

A1 = <Name> 
B1 = <Email>
C1 = New-MailContact
D1 = '-Name
E1 = '-ExternalEmailAddress
F1 = =(C1&" "&D1&" ""&A1&"" "&E1&" ""&B1&""")

My issue is that my F1 column results in the following output:

New-MailContact -Name "&A1&" -ExternalEmailAddress "&B1&"

Can someone please help me to fix the A1 and B1 records so they show up as the actual name and email rather than A1 and B1?

Sean C.
  • 23
  • 5

2 Answers2

1

You have a bunch of extra quotes

=C1 & " " & D1 & " " & A1 & " " & E1 & " " & B1

should display the data concatenated.

If you want the name or other fields to be quoted, for every quote you want add two quotes in the string:

= """" & C1 & """"

will display New-MailContact in quotes, like this "New-MailContact"

See this as " "" " where the outside quotes are to denote it as a string, and the two quotes inside are for displaying the one "

So if you need name and email (A1 and B1) quoted, you need

=C1 & " " & D1 & " """ & A1 & """ " & E1 & " """ & B1 & """"

More examples.

Community
  • 1
  • 1
Nivas
  • 18,126
  • 4
  • 62
  • 76
0

The ASCII code for a double-quote character is 22 hex or 34 dec. The ASCII character code for a space is 20 hex or 32 dec.

=C1&CHAR(32)&D1&CHAR(32)&CHAR(34)&A1&CHAR(34)&CHAR(32)&E1&CHAR(32)&CHAR(34)&B1&CHAR(34)
'alternate with CONCATENATE
=CONCATENATE(C1, CHAR(32), D1, CHAR(32), CHAR(34), A1, CHAR(34), CHAR(32), E1, CHAR(32), CHAR(34), B1, CHAR(34))
'alternate with Office 365's TEXTJOIN¹
=TEXTJOIN(CHAR(32), FALSE, C1, D1, CHAR(34)&A1&CHAR(34), E1, CHAR(34)&B1&CHAR(34))

¹ The TEXTJOIN function was introduced with Excel 2016 with Office 365 or Excel Online.

        char_34