SOLVED

Cell formatting from formula to text

New Contributor

I am trying to generate a home address cell by concatenating three separate cells with the House number, ie 1234, a Street name, and a Street type.  My formulas for the home address cell is =H2&I2&j2.  When I use the home address cell in my merge file for the address, I get the formula versus the address text, i.e.

output is:  =h2&i2&j2

desire:  1234 Main Street

 

excel file info

Address:  H2       I2        J2

Value       1234   Main   Street

4 Replies
best response confirmed by kraus2994 (New Contributor)
Solution

@kraus2994 Are you talking about "mail merge" in Word? If so, I can't replicate the issue. Inserting both the Home Adress field or each of the three underlying fields produce the correct text for the address.

 

Inserted fields in Word, Mail merge:

Screenshot 2021-02-22 at 07.23.54.png

Preview of output:

Screenshot 2021-02-22 at 07.24.05.png

 

 

 

 

 

If not, how/where are you merging the addresses?

@Riny_van_Eekelen 

Riny,

Thank you for the reply.  As it turns out, I am not using Microsoft word mail merge application.  I am using the excel file as an upload to the United States Postal Service Business Customer gateway, Intelligent mail small Business (IMsb) Tool.  Apparently this tool does not handle the generated home address cell information the same way as Microsoft word mail merge.

 

Is there an option for me to paste special my generated home address that would be representative of the text only and not the adding test "&" function formula?

 

thank you 

@kraus2994 Yes! Once you have concatenated the home addresses, say in column A, you can copy the entire column and paste special as values in a new column and point your merge tool to that new "text only" column.

@Riny_van_Eekelen 

Great Suggestion.  that worked.  I truly appreciate your excellent support which allowed me to complete my mail merge activities with the USPS

 

Mike