Forum Discussion
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?
10 Replies
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.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?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.
Hello Peter,
Sorry, but I can't seem to figure out how to implement your formula into my allready working formula
="ADRES "&A123&TEKEN(10)&C125&TEKEN(10)&"T.a.v. "&C124&" "&C123&TEKEN(10)&C126&TEKEN(10)&C127
I don't know. Does the Dymo plugin have settings that might affect this?
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).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.
bedankt Hans!
