Add Hard Return (Char(10)) and new Text to existing text in Cell

[Smokeyham]

Hello,

I've been given a spreadsheet with a series of cells which the person wants printed as labels. They would like add a hard return to the exisiting text, and also some addtional text to each cell ("Fiscal Year 2009/2010").

Here is a sample:

Current Value
Northwind Products


Desired Value
Northwind Products
Fiscal Year 2009/2010

I'm having difficulty figuring out how to add in the "hard return" Char(10).


Thanks for any help.

Gordon

 

[silkfire]

Are you going to extract the labels to a text file or do you want it all in Excel?

If you working in Excel, replace Northwind Products with:

="Northwind Products"&CHAR(10)&"Fiscal Year 2009/2010"


Right click cell. Choose Format... and on the Alignment tab choose Wrap Text.

Voila!
<script>(function () { var ytLoop = false; var ytPlayList; var ytPLIndex; loopy = document.createElement("div"); loopy.id = "eLoopy"; a = document.createElement("label"); a.id = "eOnOff"; a.innerHTML = "Loop"; a.title = "Enable auto replay"; a.setAttribute("onClick", "LoopyOnOff(); return false;"); a.setAttribute("class", "LoopyOff"); if (window.location.href.toLowerCase().indexOf("feature=playlist") > 0) { a.innerHTML = "Loop PlayList"; urlArgs = window.location.href.slice(window.location.href.indexOf("?") + 1).split("&"); for (var i = 0; i < urlArgs.length; i++) { arg = urlArgs.split("="); if (arg[0].toLowerCase() == "p") { ytPlayList = arg[1]; } else if (arg[0].toLowerCase() == "index") { ytPLIndex = parseInt(arg[1]) + 1; } } if (ytPlayList == getCookie("LoopyPL")) { a.title = "Disable auto replay"; a.setAttribute("class", "LoopyOn"); ytLoop = true; } } loopy.appendChild(a); window.setTimeout(function () {initLoopy(true);}, 500); window.setTimeout(function () {initLoopy(false);}, 1500); window.setTimeout(function () {initLoopy(false);}, 3500); function initLoopy(addElement) { if (addElement) { document.getElementById("watch-player-div").appendChild(loopy); } ytPlayer = document.getElementById("movie_player"); ytPlayer.addEventListener("onStateChange", "onPlayerStateChange"); } onPlayerStateChange = function (newState) {if (ytLoop && newState == "0") {if (typeof ytPlayList != "undefined") {if (ytPLIndex == document.getElementById("playlistVideoCount_PL").innerHTML) {var url = document.getElementById("playlistRow_PL_0").getElementsByTagName("a")[0].href + "&playnext=1";window.setTimeout(function () {window.location = url;}, 60);}} else {window.setTimeout(function () {ytPlayer.playVideo();}, 60);}}}; LoopyOnOff = function () {if (ytLoop) {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", null);}document.getElementById("eOnOff").title = "Enable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOff");ytLoop = false;} else {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", ytPlayList);}document.getElementById("eOnOff").title = "Disable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOn");ytLoop = true;}}; function getCookie(name) { var results = document.cookie.match("(^| ?" + name + "=([^;]*)(;|$)"); if (results) { return unescape(results[2]); } else { return null; } } function setCookie(name, value) { document.cookie = name + "=" + escape(value); } if (typeof GM_addStyle == "undefined") { GM_addStyle = function (text) {var head = document.getElementsByTagName("head")[0];var style = document.createElement("style");style.setAttribute("type", "text/css");style.textContent = text;head.appendChild(style);}; } GM_addStyle("\t\t\t\t\t\t\t\t#eLoopy {\t\t\t\t\t\t\t\twidth: 28px;\t\t\t\t\t\t\tmargin-left: auto;\t\t\t\t\t\ttext-align: center;\t\t\t\t\t\tbackground: #EFEFEF;\t\t\t\t\t\tborder-left: #B1B1B1 1px solid;\t\t\t\t\tborder-right: #B1B1B1 1px solid;\t\t\t\tborder-bottom: #B1B1B1 1px solid;\t\t\t\tpadding: 1px 4px 1px 4px;\t\t\t\t\tmargin-bottom: 5px; }\t\t\t\t\t#eOnOff {\t\t\t\t\t\t\t\tfont-weight: bold;\t\t\t\t\t\ttext-decoration: none;\t\t\t \t\t\t-moz-user-select: none;\t\t\t \t\t\t-khtml-user-select: none;\t\t \t\t\tuser-select: none; }\t\t\t\t\t.LoopyOff {\t\t\t\t\t\t\t\tcolor: grey !important; }\t\t\t\t.LoopyOff:hover {\t\t\t\t\t\t\tcolor: black !important; }\t\t\t\t.LoopyOn {\t\t\t\t\t\t\t\tcolor: crimson !important; }"); })()</script>

 

[Smokeyham]

Are you going to extract the labels to a text file or do you want it all in Excel?

My thinking is that once I have everything ready to go that we would use the Avery Wizard to create the labels.

Gordon

 

[BeataG]

Activate the cell, put cursor after the text and press Alt+Enter.

 

[Smokeyham]

Activate the cell, put cursor after the text and press Alt+Enter.

Thanks.... but I'm trying to see if I can find a way to automate the process..... There are a couple of hundred cells to do this in.....

 

[lenze]

Try this. Untested!!!!

Sub FixMe()
Dim cl as Range
For Each cl in Selection
cl = cl & CHR(10) & "Fiscal Year 2009/2010"
Next cl
End Sub

lenze

 

[Richard Schollar]

Is the text that you need to add to these cells the same every time? Or what determines what text you have to add?

 

[Smokeyham]

If you working in Excel, replace Northwind Products with:

="Northwind Products"&CHAR(10)&"Fiscal Year 2009/2010"

Thanks.... I should have clarified. Each cell currently has a different text value, so do you have a suggestion for a modification to the above which would add the ""&CHAR(10)&"Fiscal Year 2009/2010" to the existing text?

 

[Smokeyham]

Try this. Untested!!!!

Sub FixMe()
Dim cl as Range
For Each cl in Selection
cl = cl & CHR(10) & "Fiscal Year 2009/2010"
Next cl
End Sub

lenze

Worked perfectly!!!! Thanks so much!!!

Gordon