Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 07-02-2008, 11:51 AM
Bandgeek050 Bandgeek050 is offline
Member
SOA
 
Join Date: May 2007
Studying for NOTHING
College: UNL Alum
Posts: 373
Default Text to columns

Question: anyone know a way to do a text to rows type function? I have multiple cells with 12 data numbers in each individual cell and I want to move them into 12 rows. They are separated within the cell by a return so doing text to columns and then transposing isn't working either.

I can do it "brut force methond" by selecting the values in the cell, moving down one and pasting them but I'm just trying to find a quicker way.

Thanks
Reply With Quote
  #2  
Old 07-02-2008, 01:18 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

Hi Bandgeek, try this. (I just tried it and it appears to work)

Highlight the cells in Excel. Copy.
Open a blank Word document. Paste.
Select the table that Word just created. Copy.
Go back to Excel (in a blank area of the sheet). Paste.

I ended up with 12 separate cells with one number in each. (E.g. no longer just one cell with 12 numbers separated by returns)

Hope it works for you.
Reply With Quote
  #3  
Old 07-02-2008, 01:37 PM
Ron Weasley's Avatar
Ron Weasley Ron Weasley is offline
Member
CAS AAA
 
Join Date: Oct 2001
Studying for naught.
Favorite beer: Butterbeer
Posts: 8,623
Default

You could also try this function to replace the carriage return with a space:

=SUBSTITUTE(H12,"
"," ")

After the =SUBSTITUTE(H12,"
use <alt><enter> to enter a carriage return character when your editing the formula.

This will give you a typical row of text that can then be operated on by the "text to columns" functionality of Excel.

RW
Reply With Quote
  #4  
Old 07-02-2008, 01:48 PM
Bandgeek050 Bandgeek050 is offline
Member
SOA
 
Join Date: May 2007
Studying for NOTHING
College: UNL Alum
Posts: 373
Default

Thanks.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 02:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.38405 seconds with 11 queries