Forum Discussion

Marco_CV's avatar
Marco_CV
Copper Contributor
Nov 06, 2024

Text wrapping in a cel with formula

Hello,

I'm new on this community so please don't offend if I approach my problem in an incorrect way.
I've been looking in previous threads to find an answer, but can't seem to find it.

I have an Office365 Excel sheet with separated collums like Company, Contactperson, Address, City and Country.
I want to combine data from the different collums in 1 cell, for instance with the following formula:
="Company: " &A1 &", Contact: " &B1 &", Address: " &C1 &", City: "&D1 &", Country: " &E1

This results in the following information:
Company: CompanyName Ltd., Contact mr. John Smith, 32 Church Lane, Amsterdam, THE NETHERLANDS


So far so good, but I want the result lines in the cell to be wrapped so every line is underneath eachother, like this:
Company: CompanyName Ltd.
Contact: mr. John Smith
Address: 32 Church Lane
City: Amsterdam
Country: NETHERLANDS

As far as I have seen, a break line in an Excelcell can be done by using CHAR(10)
So, I thought to set the cell on "Wrap text" and use the following formula:
="Company: " &A1 &CHAR(10) &"Contact: " &B1 &CHAR(10) &"Address: " &C1 &CHAR(10) &"City: " &D1 &CHAR(10) &"Country: " &E1


However, this formula is not accepted and results in #NAME?

Does anyone have an idea what I'm doing wrong and how to get the result I'm looking for?

excel
office 365
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 06, 2024

    The formula works in the English language version of Excel. Do you use a different language? If so, you should use the local version of CHAR. For example, in German it is ZEICHEN, in Dutch TEKEN, and in French CAR.

  • Marco_CV's avatar
    Marco_CV
    Copper Contributor
    Nov 06, 2024

    Problem solved... it has to do with the language settings.
    My software is set in Dutch so I need to use the Dutch command TEKEN(10) instead of the English version CHAR(10).

  • Marco_CV's avatar
    Marco_CV
    Copper Contributor
    Nov 06, 2024

    The information in the cell works just fine now as it should.

    Now I need to print the cell with multi-line information on a DYMO LabelWriter 400.
    There is a special Dymo plugin available for Excel that I have installed.

    However, when sending the the printjob, every line from the Excell cell is splitted into a separate label, problably because of the TEKEN(10) hard enter after every line.
    Is there a way to solve this by telling Excel that it needs to be printed as 1 complete textblock?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Nov 06, 2024

      I don't know. Does the Dymo plugin have settings that might affect this?

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 06, 2024

      If your Dymo app is picking up the Line feeds, then an alternative might be to pad each line to the same (maximum allowable) length using

      = CONCAT(LEFT(header & ": " & Address & REPT(" ", 32), 32))

where header
= {"Company","Contact","Address","City   ","Country"}

      It is such a basic requirement, though, that the app must accept some combination of <LF>, <CR> and <FF> as control characters.

      • Marco_CV's avatar
        Marco_CV
        Copper Contributor
        Nov 11, 2024

        Hello Peter,

        Thank you for your feedback, I will go and try this as well!

        KR Marco

  • Marco_CV's avatar
    Marco_CV
    Copper Contributor
    Nov 06, 2024

    Helaas niet... vandaar dat ik me afvroeg of het wellicht binnen Excel te regelen viel.
    Anders is het een kwestie van de cel kopiëren en in een bijvoorbeeld kladblok plakken, dan is het gewoon omgezet naar platte tekst en gaat het wel goed op de labelprinter.