Process for splitting information in a cell(s)
 

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

One thing to try (on a backup copy of your data) is the TRIM function, which
strips text strings of unprintable characters. A similar function is the
CLEAN function.

Try those and see if either or both work.

Dave
--
Brevity is the soul of wit.


"yamefui" wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will delete any specific character(s) you wish..........

Vaya con Dios,
Chuck, CABGx3



"yamefui" wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

Actually, I made a mistake. TRIM removes spaces in a text string. CLEAN
removes unprintable characters. You probably want to use CLEAN.

Apologies.

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

One thing to try (on a backup copy of your data) is the TRIM function, which
strips text strings of unprintable characters. A similar function is the
CLEAN function.

Try those and see if either or both work.

Dave
--
Brevity is the soul of wit.


"yamefui" wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

Hi Dave, thanks for the response. When I use CLEAN (it works ok) to remove
the characters (boxes) and then attempt to use the TEXT TO COLUMNS feature I
cannot because CLEAN removes the characters in the cell for some reason. All
is see, for example is =CLEAN(A15) and not the original information in the
cell, something like this (Fenton, Jody JamesNorth Nouth Properties, Inc.113
BroadwayPO Box 9Wilbur, CO 81688).

Am I missing a step here? Thanks.

jon


"Dave F" wrote:

Actually, I made a mistake. TRIM removes spaces in a text string. CLEAN
removes unprintable characters. You probably want to use CLEAN.

Apologies.

Dave
--
Brevity is the soul of wit.


"Dave F" wrote:

One thing to try (on a backup copy of your data) is the TRIM function, which
strips text strings of unprintable characters. A similar function is the
CLEAN function.

Try those and see if either or both work.

Dave
--
Brevity is the soul of wit.


"yamefui" wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

i had a similar problem. you can go to chip pearson site and download the
"cell view add-in" this will give you the actual character to look for like
"Alt-155" for the ¢ sign and so forth. You could then use Find/Replace as
Alt0155 as above. Another approach is to use the ASAPutilities.com free
add-in to strip all non-printing characters.

If you are working with imported data or lots of stuff off the web, both of
these free add-ins are essential in my opinion.

"Dave F" wrote:

One thing to try (on a backup copy of your data) is the TRIM function, which
strips text strings of unprintable characters. A similar function is the
CLEAN function.

Try those and see if either or both work.

Dave
--
Brevity is the soul of wit.


"yamefui" wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

--
jon

Process for splitting information in a cell(s)
 

yamefui wrote:
Hi Dave, thanks for the response. When I use CLEAN (it works ok) to remove
the characters (boxes) and then attempt to use the TEXT TO COLUMNS feature I
cannot because CLEAN removes the characters in the cell for some reason. All
is see, for example is =CLEAN(A15) and not the original information in the
cell, something like this (Fenton, Jody JamesNorth Nouth Properties, Inc.113
BroadwayPO Box 9Wilbur, CO 81688).

Am I missing a step here? Thanks.

jon

Hi jon,

Before attempting to use the TEXT TO COLUMNS you should copy that
column of data, to which you have applied the CLEAN function, then
Paste Special|Paste Values. After that you should see the data, and not
the formula, in the TEXT TO COLUMNS dialogs.

Ken Johnson

Process for splitting information in a cell(s)
 

On Fri, 5 Jan 2007 08:39:01 -0800, yamefui wrote:

Hello, in a series of spreadsheet cells (format is general text) is an
example below of the information contained in each. However, separating each
line of data (name, company, address, city-state-zip) there are one or two
small block shaped outlines, about 1/2 the size of the letters which may be
present due to the previous format in which the information was contained. I
cannot copy-paste them here for you to see as this online format will not
accept them.

Johnson, Jimmy (2 small blocks)
Acme Realtors, L.L.C. (2 small blocks)
1582 Oak Drive (2 small blocks)
Hotlanta, GA 30093 (2 small blocks)

I am trying to split this information into separate columns for first name,
last name, company, street address, city, state zipcode but cannot (using the
Text to Columns option in the Data menu (delimited function). When I attempt
this and step through the Convert text to Columns wizard the only information
that remains is the name information in this format/result (Johnson, Jimmy).
All the other information disappears and I don't know why (neither does the
original creator of the spreadsheet). I cannot use the Fixed Width option
because the data in the 8000+ cells is so skewed it cannot be separated
properly.

Any ideas on how I can find a workable solution? Please advise if the
description I provided needs clarification.

Are all four lines above in the same cell?

You could use CODE(MID(cell_ref,15,1)) or something similar to see what those
two blocks are. Then use that code as a delimiter in the Data/Text to Columns
wizard.


--ron