How to parse text in a cell

  • Thread starter Thread starter Rob
  • Start date Start date
R

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
 
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
Click to expand...

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.
 
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!
 
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
Click to expand...

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
 
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)
 
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)
Click to expand...

Brilliant! Thank you, Rick!
 
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
Click to expand...

Garry,

Thank you, the <Alt+Enter> works fine...

Rob
 
Rob has brought this to us :
Garry,

Thank you, the <Alt+Enter> works fine...

Rob
Click to expand...

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 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 -
Click to expand...

OK, got it...

Thank you
 
Back
Top