Split Description into 3 Columns

**Vinnie Chan** · 12-09-2009, 07:55 PM

Hi,

I need help to split text (shown below) into 3 columns, now all text is in one cell.

e.g.,

HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286

I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.

Thank you.

**jrdnoland** · 12-09-2009, 08:29 PM

Have you tried "Text To Columns" function? I was able to split your example worksheet using it.

**darkyam** · 12-10-2009, 12:01 AM

Depending on how you implement jrdnoland's suggestion, you might have to concatenate a few of the columns to get your desired results. Could you please supply a few examples of the results you'd like so we know where to split the data? It's very likely we can do it with formulas or possibly a couple quick Find and Replaces (if this only has to be done once) and save you the extra steps of separating into columns and then rejoining.

**Vinnie Chan** · 12-10-2009, 08:57 AM

Thank you for your reply.

I have used "Text to Column" for part of the file, but not very successful.
I need to do this monthly so a time-saving method is needed.

I'm attaching the exmple file for you, so you'll know better how I would like to split the text.

Thanks again.

**martindwilson** · 12-10-2009, 09:47 AM

try find /replace
for find in find window
hit space bar 4 times
replace ^
text to columns
delimited
other ^
check treat succesive delimiters as on click finish
then come back and say what you need done next

**royUK** · 12-10-2009, 09:53 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I'll edit this time!

**Vinnie Chan** · 12-10-2009, 11:07 AM

Hi martindwilson,

I've tried what you suggested, but didn't really work. Don't know if I've misunderstood what you mean. I'm attaching both files for you.

Thanks for your help.

**martindwilson** · 12-10-2009, 11:13 AM

attached is the results of first attempt is this on the right track?

**Vinnie Chan** · 12-10-2009, 12:06 PM

Hi martindwilson,

I don't think this is exactly what I want.

I want to split a group of words in a cell with spaces in between some of the words. There are over 30000 rows of data in this file with different numbers of spaces among words.

e.g. col 1 = description col 2 = brand col 3 = sku # or PC #

Hope this is much clear about what I'm looking for.

Thanks for your help.

**martindwilson** · 12-10-2009, 12:20 PM

i would help if yo give some examples of what it should look like but id start by getting all those that have a price together looks like cigarettes only and deal with those seperately then those that dont. use =ISNUMBER(FIND(CHAR(254),A1)) drag down
and sort all the true will go together

**Vinnie Chan** · 12-10-2009, 12:42 PM

Hi martindwilson,

Please see my attached file. I want to split Col A into Col B, Col C and may be Col D if there are numbers or PC #s.

Hope this explains better of what I hope to see.

Thank you.

**martindwilson** · 12-10-2009, 01:01 PM

there are too many variables to do this as nOthing seperates or distinguishes for example
E 60 G 3 TELL BTL TROCKENSUPPEN you cant tel where it should split.
but you have some strange spacing isuggesting this is imported from somewhere thats in the correct format?

**Vinnie Chan** · 12-10-2009, 03:34 PM

Yes, this is imported from some other software from other country. I have no idea what software they use.

**martindwilson** · 12-10-2009, 05:01 PM

perhaps if you could find the original format it may be possible to import to excel directly

Split Description into 3 Columns

LinkBack

Thread Tools

Rate This Thread

Display

Split Description into 3 Columns

re: Split Description into 3 Columns

re: Split Description into 3 Columns

re: Split Description into 3 Columns

re: Split Description into 3 Columns

re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Re: Split Description into 3 Columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions