How to parse text in a cell

[Rob]

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

 

[GS]

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.

 

[GS]

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!

 

[Andrew]

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

 

[Rick Rothstein]

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)

 

[GS]

Rick Rothstein pretended :

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!

 

[Rob]

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

 

[GS]

Rob has brought this to us :

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>

 

[Rob]

Rob has brought this to us :







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