text to columns w a line break


G

Guest

Good morning,

I'm trying to separate a worksheet containing single cells with mutiple columns of data lumped together. These are some names and addresses, states, zips, all in a single cell for each entry. Looks like this

'Sample Company<strange character>Street Address<strange character>City<strange character>State<strange character>zip

Problem is, there is a strange "square" or block character that denotes the separation of each field. I am told tis is a line break. Text to columns does not recognize this character, and I'm unsure of which character to enter into "other". Does anyone have an idea of what this character is, and/or how to replace it with a comma ? or... a utility or other application to cleanse this data?

Thanks for reading & helping.

DR
 
Ad

Advertisements

K

Kathy

Try this work around.
Copy the "strange square" and paste in the Find replace
option. Replace with a "," then run the Text to Column
wizard.
-----Original Message-----
Good morning,

I'm trying to separate a worksheet containing single
cells with mutiple columns of data lumped together.
These are some names and addresses, states, zips, all in
a single cell for each entry. Looks like this
'Sample Company<strange character>Street Address<strange
character>City said:
Problem is, there is a strange "square" or block
character that denotes the separation of each field. I am
told tis is a line break. Text to columns does not
recognize this character, and I'm unsure of which
character to enter into "other". Does anyone have an
idea of what this character is, and/or how to replace it
with a comma ? or... a utility or other application to
cleanse this data?
 
G

Gord Dibben

DR

In the "other" box type 0010 on the NumPad while holding the ALT key down.

You won't see anything in the box, but your data will get separated.

Gord Dibben Excel MVP
 
P

Peo Sjoblom

What if there are other a in the cell/column? One way assuming it is a
carriage return,
select delimited, click next and as delimiter select other, place the cursor
there and hold down alt while typing 010 on the numpad (numeric keypad),
release the alt button. That works if these are carriage returns.


Regards,

Peo Sjoblom
 
P

Peo Sjoblom

Save one key press since 010 will do it

--

Regards,

Peo Sjoblom


DR

In the "other" box type 0010 on the NumPad while holding the ALT key down.

You won't see anything in the box, but your data will get separated.

Gord Dibben Excel MVP
columns of data lumped together. These are some names and addresses,
states, zips, all in a single cell for each entry. Looks like thisthe separation of each field. I am told tis is a line break. Text to
columns does not recognize this character, and I'm unsure of which character
to enter into "other". Does anyone have an idea of what this character is,
and/or how to replace it with a comma ? or... a utility or other application
to cleanse this data?
 
G

Guest

This was the fix - thank you so very much

I have also learned "substitute" can do this too, but is not nearly so quick, or elegant!
 
Ad

Advertisements

G

Gord Dibben

Thanks Peo

I didn't know that(I think).

How 'bout that Naslund, eh? 4G last against Pittsburgh

Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top