Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
I have a ranking report where the user selects a sort basis from a
drop-down control. In one cell I spell out what the sort basis is using the following formula: ="Retailer sort based on: "&list!A26 where the sort basis is listed in cell list!A26 And looks like this: Retailer sort based on: Sales Rate Index However I prefer the appearance to be like this: Retailer sort based on: Sales Rate Index In the days of Lotus I used the "^" to separate the text...but for the life of me cannot figure a simple way to do this in Excel. Any ideas? TIA Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
Rob wrote on 9/2/2011 :
I have a ranking report where the user selects a sort basis from a drop-down control. In one cell I spell out what the sort basis is using the following formula: ="Retailer sort based on: "&list!A26 where the sort basis is listed in cell list!A26 And looks like this: Retailer sort based on: Sales Rate Index However I prefer the appearance to be like this: Retailer sort based on: Sales Rate Index In the days of Lotus I used the "^" to separate the text...but for the life of me cannot figure a simple way to do this in Excel. Any ideas? TIA Rob Depending on where you want to enter a new line, while typing your formula use 'Alt+Enter' to insert the new line at the desired position. It looks like you want the second line to 'appear' indented and so... ="Retailer sort based on:<Alt+Enter "&list!A26 ...where you use the keyboard combination AFTER ":", followed by 2 spaces and the closing double quote character. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
Well.., that doesn't work when using a formula and so I guess TextWrap
is your only option in a single cell. It doesn't look like you'll get any indentation on line 2 either. Maybe 2 cells is a better choice! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
On Sep 2, 4:03*pm, Rob wrote:
I have a ranking report where the user selects a sort basis from a drop-down control. In one cell I spell out what the sort basis is using the following formula: ="Retailer sort based on: "&list!A26 where the sort basis is listed in cell list!A26 And looks like this: Retailer sort based on: Sales Rate Index However I prefer the appearance to be like this: Retailer sort based on: * *Sales Rate Index In the days of Lotus I used the "^" to separate the text...but for the life of me cannot figure a simple way to do this in Excel. Any ideas? TIA Rob This is a good function for parsing strings. Put a string into cell 1,1 and run this code. The Split(TXT,sep) takes the string TXT and breaks it at each occurrence of the marker "sep". The result is a string array. Andy Dim txt As String Dim x As Variant Dim i As Long Dim q As Integer txt = Cells(1, 1).Value x = Split(txt, " ") For i = 0 To UBound(x) Cells(2+i, 1) = x(i) Next i |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
It doesn't look like you'll get any indentation on line 2 either.
After word wrap is turned on (and the column is made wide enough), this will split the text and indent the second line two spaces... ="Retailer sort based on:"&CHAR(10)&" "&List!A26 Rick Rothstein (MVP - Excel) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
Rick Rothstein pretended :
It doesn't look like you'll get any indentation on line 2 either. After word wrap is turned on (and the column is made wide enough), this will split the text and indent the second line two spaces... ="Retailer sort based on:"&CHAR(10)&" "&List!A26 Rick Rothstein (MVP - Excel) Brilliant! Thank you, Rick! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
On Sep 2, 3:34*pm, GS wrote:
Well.., that doesn't work when using a formula and so I guess TextWrap is your only option in a single cell. It doesn't look like you'll get any indentation on line 2 either. Maybe 2 cells is a better choice! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Garry, Thank you, the <Alt+Enter works fine... Rob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
Rob has brought this to us :
On Sep 2, 3:34*pm, GS wrote: Well.., that doesn't work when using a formula and so I guess TextWrap is your only option in a single cell. It doesn't look like you'll get any indentation on line 2 either. Maybe 2 cells is a better choice! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Garry, Thank you, the <Alt+Enter works fine... Rob I found it doesn't ALWAYS work fine in the case of a formula, and so I strongly recommend you use Rick's sage suggestion to specify the newline character.<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to parse text in a cell
On Sep 6, 8:46*am, GS wrote:
Rob has brought this to us : On Sep 2, 3:34*pm, GS wrote: Well.., that doesn't work when using a formula and so I guess TextWrap is your only option in a single cell. It doesn't look like you'll get any indentation on line 2 either. Maybe 2 cells is a better choice! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Garry, Thank you, the <Alt+Enter works fine... Rob I found it doesn't ALWAYS work fine in the case of a formula, and so I strongly recommend you use Rick's sage suggestion to specify the newline character.<g -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - OK, got it... Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse only text from column? | Excel Programming | |||
Parse Text | Excel Discussion (Misc queries) | |||
Parse text | Excel Discussion (Misc queries) | |||
WITHIN CELL, PARSE END OF TEXT, START OF NUMBER | Excel Discussion (Misc queries) | |||
How can I parse time and address from cell with non standard extra text? | Excel Programming |