How can I break values apart that are in the same cell?

[Guest]

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?

 

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

Thanks, Duke. That did the trick!

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.