Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 4th 05, 03:00 PM
Phil
 
Posts: n/a
Default How can I break values apart that are in the same cell?

Hello,

I have recieved a table that has multiple values in the same cell. The
table (named Permits) contains one col (named Tract_ID). Presumably, the
user used ALT-Enter to nest the values in the same cell. So a typical cell
would contain values like this:

"7-5-029-002
7-5-030-002
7-5-031-001
7-5-032-001"

This type of "multiple" entry values occurs about half of the time on this
particular spreadsheet. The other half of the time, there will be only one
value in the Tract_ID col, like this: 7-5-029-015. Now if you try and save
the spreadsheet with the multiple value cells to a text file then try to
re-import it, it shows the multiple-value field values separated by a square
box (wish I knew what to call it - if anyone knows what it is called, please
tell me). Also, if I try to import the spreadsheet into Access, it shows
that little square character.

Here is my question: Is there a way to run a function such that it will take
the 1st (of the 4 aforementioned) value, and put it in a separate column,
then repeat that until all 4 values are in their own separate columns?

  #2   Report Post  
Old October 4th 05, 03:26 PM
Duke Carey
 
Posts: n/a
Default

You can do a search & replace, substituting some other unique char for the
Alt-Enter combo, then do a text to column on the revised data

To replace the Alt-Enters, click in the Search for box and, while holding
the Alt key, use the numeric keypad to type 0010. Use some unique character
in the Repace box and you should be good to go.


"Phil" wrote:

Hello,

I have recieved a table that has multiple values in the same cell. The
table (named Permits) contains one col (named Tract_ID). Presumably, the
user used ALT-Enter to nest the values in the same cell. So a typical cell
would contain values like this:

"7-5-029-002
7-5-030-002
7-5-031-001
7-5-032-001"

This type of "multiple" entry values occurs about half of the time on this
particular spreadsheet. The other half of the time, there will be only one
value in the Tract_ID col, like this: 7-5-029-015. Now if you try and save
the spreadsheet with the multiple value cells to a text file then try to
re-import it, it shows the multiple-value field values separated by a square
box (wish I knew what to call it - if anyone knows what it is called, please
tell me). Also, if I try to import the spreadsheet into Access, it shows
that little square character.

Here is my question: Is there a way to run a function such that it will take
the 1st (of the 4 aforementioned) value, and put it in a separate column,
then repeat that until all 4 values are in their own separate columns?

  #3   Report Post  
Old October 4th 05, 03:28 PM
Roger Govier
 
Posts: n/a
Default

Hi Phil

The square is the symbol for line feed or Char(10)
With your data in column A

B1 =LEFT(A1,11)
C1=MID(A1,13,11)
D1=MID(A1,25,11)
E1=RIGHT(A1,11)

Copy down the page as far as you wish.
Those cells with a single value only, will just populate column B

Regards

Roger Govier



Phil wrote:

Hello,

I have recieved a table that has multiple values in the same cell. The
table (named Permits) contains one col (named Tract_ID). Presumably, the
user used ALT-Enter to nest the values in the same cell. So a typical cell
would contain values like this:

"7-5-029-002
7-5-030-002
7-5-031-001
7-5-032-001"

This type of "multiple" entry values occurs about half of the time on this
particular spreadsheet. The other half of the time, there will be only one
value in the Tract_ID col, like this: 7-5-029-015. Now if you try and save
the spreadsheet with the multiple value cells to a text file then try to
re-import it, it shows the multiple-value field values separated by a square
box (wish I knew what to call it - if anyone knows what it is called, please
tell me). Also, if I try to import the spreadsheet into Access, it shows
that little square character.

Here is my question: Is there a way to run a function such that it will take
the 1st (of the 4 aforementioned) value, and put it in a separate column,
then repeat that until all 4 values are in their own separate columns?


  #4   Report Post  
Old October 4th 05, 03:41 PM
Phil
 
Posts: n/a
Default

Thanks, Duke. That did the trick!

"Duke Carey" wrote:

You can do a search & replace, substituting some other unique char for the
Alt-Enter combo, then do a text to column on the revised data

To replace the Alt-Enters, click in the Search for box and, while holding
the Alt key, use the numeric keypad to type 0010. Use some unique character
in the Repace box and you should be good to go.


"Phil" wrote:

Hello,

I have recieved a table that has multiple values in the same cell. The
table (named Permits) contains one col (named Tract_ID). Presumably, the
user used ALT-Enter to nest the values in the same cell. So a typical cell
would contain values like this:

"7-5-029-002
7-5-030-002
7-5-031-001
7-5-032-001"

This type of "multiple" entry values occurs about half of the time on this
particular spreadsheet. The other half of the time, there will be only one
value in the Tract_ID col, like this: 7-5-029-015. Now if you try and save
the spreadsheet with the multiple value cells to a text file then try to
re-import it, it shows the multiple-value field values separated by a square
box (wish I knew what to call it - if anyone knows what it is called, please
tell me). Also, if I try to import the spreadsheet into Access, it shows
that little square character.

Here is my question: Is there a way to run a function such that it will take
the 1st (of the 4 aforementioned) value, and put it in a separate column,
then repeat that until all 4 values are in their own separate columns?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Min values in a list of numbers Traima Excel Worksheet Functions 1 August 5th 05 01:32 PM
Count Intervals of Filtered TEXT values in Column and Return Count across a Row Sam via OfficeKB.com Excel Worksheet Functions 9 July 31st 05 03:37 AM
Combine FREQUENCY and SUM of Associated Values MichaelC Excel Worksheet Functions 3 July 3rd 05 01:54 AM
Using Match function with duplicate values in an array Richard Excel Worksheet Functions 2 June 30th 05 10:20 PM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM


All times are GMT +1. The time now is 08:18 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017