Text to columns fixed width and delimited

**bradvh0023** · 05-10-2011, 10:56 AM

Hello,

I have an automated program through our company's website that spits out a form with various contact information. I now want to import all this information into excel, however it is entered into one cell, in which text to columns (delimited or fixed width) do not work. Does anyone have a macro that could split this up? Fields are First name, last name, email, age, gender, city, state, zip, receive offers Example below....

Thanks!

' cindy

skafio

[email protected]

50-59

F

7-15 164th ave

brooklyn

NY

11314

Yes

**NBVC** · 05-10-2011, 11:04 AM

Is there a special character between each string to separate, like a carriage return or line feed... Code 10 or 160? If so, then you might be able to use Text to columns... if not, how do you know the break points?

**bradvh0023** · 05-10-2011, 11:15 AM

There is not any special characters between lines of text, that is why I cannot use text to columns. I think only a macro will work but I am not familiar with that side of excel. Thanks for the quick response however.

**bradvh0023** · 05-10-2011, 11:16 AM

Then again, I'm not quite sure what a line feed or carriage return are.... so if you could explain that then maybe I can check?

**ChemistB** · 05-10-2011, 11:30 AM

Line feeds or carriage returns might look like simple spaces between words. Let's say you have imported data in cell A1. Joan Rivers dddd ddddd. We want to look at that first space which is in position 5 in this case.
In another cell, enter
=CODE(MID(A1,5,1)) and see what you get. 32 is a space.

**Ron Coderre** · 05-10-2011, 11:49 AM

I think this gets you most of the way....
With
A1:A10 containing your downloaded text, each cell contains all of the fields for one person

Using this setup

Please Login or Register  to view this content.

This regular formula replaces all carriage returns with a series of spaces

Please Login or Register  to view this content.

This regular formula begins the parsing process by returning the FirstName

Please Login or Register  to view this content.

Copy that formula across through M2

Copy C2:M2 and paste down through 10 rows

The results should be a table of information for each cell in Col_A

Is that something you can work with?

**NBVC** · 05-10-2011, 12:04 PM

Couldn't you simply use this then if the separator is CHAR(10)

=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied across as far as needed where A1 contains first entry

Then copied down for each column A item...

**bradvh0023** · 05-10-2011, 12:33 PM

Originally Posted by NBVC

Couldn't you simply use this then if the separator is CHAR(10)

=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied across as far as needed where A1 contains first entry

Then copied down for each column A item...

Awesome, this seems to work great. If possible, it would be better if the equation didn't take into account the title for each one. Ex. First Name: Brad, Last Name: Smith --> is how it is being separated currently. Can it just take the actual inputs? Thanks!

**bradvh0023** · 05-10-2011, 12:35 PM

This is the information I need to split...

'First Name: John

Last Name: Griff

Email Address: [email protected]

Age Group: 30-39

Gender: M

Street Address: 30 Miles Road

Apt.or Dorm No.:

City: Kesaw

State: RI

Zip Code: 30144

Opt-in: Yes

Referring site:

**NBVC** · 05-10-2011, 12:45 PM

Try:

Please Login or Register  to view this content.

**Ron Coderre** · 05-10-2011, 01:05 PM

Originally Posted by NBVC

Couldn't you simply use this then if the separator is CHAR(10)

=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied across as far as needed where A1 contains first entry

Then copied down for each column A item...

Conceivably, yes...as long as no cell has a length greater than 100. Since posted examples seldom mimic actual scenarios perfectly, I went with the approach that's flexible enough to accomodate any length. The formula I posted can also be tweaked to handle a fixed numbeer of blank rows between cells, if necessary. You're probably right, though. The simpler formula might be all that's needed.

**bradvh0023** · 05-10-2011, 01:06 PM

Wow, you are a life saver. Thanks! I only see one error in that after the "Age" title, excel is pulling an invalid #Value, however it continues on to gender in the next column none the less.

**bradvh0023** · 05-10-2011, 01:07 PM

Originally Posted by NBVC

Try:

Please Login or Register  to view this content.

Last response was to NBVC

**NBVC** · 05-10-2011, 01:13 PM

As Ron has eluded to... maybe better to use LEN($A1) instead of 100...

so try:

Please Login or Register  to view this content.

copied across and down

**bradvh0023** · 05-10-2011, 01:22 PM

Originally Posted by NBVC

As Ron has eluded to... maybe better to use LEN($A1) instead of 100...

so try:

Please Login or Register  to view this content.

copied across and down

NBVC, you just made me a hero at a job I started this week. You are now my hero. Thank you so much I really appreciate it.

**NBVC** · 05-10-2011, 01:43 PM

You are very welcome... glad that I helped make you the hero

Please remember to mark your thread as solved....

**bradvh0023** · 05-11-2011, 02:25 PM

Originally Posted by bradvh0023

NBVC, you just made me a hero at a job I started this week. You are now my hero. Thank you so much I really appreciate it.

Hello again,

The list of over 4,000 contacts separated beautifully with the help of your code. I have now run into another problem and was hoping you may have yet another answer. I need to use all of the emails for mail-merge, but there is a few characters showing as question marks with boxes around them in front or behind of each email, thus not allowing Outlook to recognize them as emails. Any thoughts?

**NBVC** · 05-11-2011, 02:30 PM

Try using the SUBSTITUTE function.

e.g.

=SUBSTITUTE(A1,"?")

This replace all ? with blanks, if any..in cell A1

**bradvh0023** · 05-12-2011, 04:07 PM

Originally Posted by NBVC

Try using the SUBSTITUTE function.

e.g.

=SUBSTITUTE(A1,"?")

This replace all ? with blanks, if any..in cell A1

SUBSTITUTE function does not work. There are spaces before and after the email addresses with unknown characters, that cannot be replaced with a space because it will still be unrecognized as an email. the following is how the email appears in the cell. As you will notice there are spaces on either side of the email, thus not allowing to click on the email as a link... any thoughts?

" [email protected] "

**NBVC** · 05-13-2011, 07:51 AM

I misinterpreted your post as saying there were question marks. I didn't catch that they were question marks within boxes...

What do these formulas yield?

=CODE(LEFT(A1))

=CODE(RIGHT(A1))

where A1 contains one of those addresses.

**bradvh0023** · 05-13-2011, 09:07 AM

Originally Posted by NBVC

I misinterpreted your post as saying there were question marks. I didn't catch that they were question marks within boxes...

What do these formulas yield?

=CODE(LEFT(A1))

=CODE(RIGHT(A1))

where A1 contains one of those addresses.

=code(right("address") yields 13
=code(left("address") yields 9

**NBVC** · 05-13-2011, 09:38 AM

Try:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(9),"")

**bradvh0023** · 05-13-2011, 11:47 AM

Originally Posted by NBVC

Try:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(9),"")

No, still not doing the job. All of the emails are separate lengths which I don't think translates with that equation

**NBVC** · 05-13-2011, 12:03 PM

The formula has nothing to do with length... it just replaces those characters wherever they occur in the string.

Any way you can attach a sample in a workbook? Just change actual email address... only a few representative samples should do...

**bradvh0023** · 05-13-2011, 01:12 PM

Originally Posted by NBVC

The formula has nothing to do with length... it just replaces those characters wherever they occur in the string.

Any way you can attach a sample in a workbook? Just change actual email address... only a few representative samples should do...

attached.....

all I need to do is be able to have the e-mails appear as links ... "bad" characters in names, etc. are not as important

I really appreciate all of your help again! test.xls

**NBVC** · 05-13-2011, 01:30 PM

Try:

=HYPERLINK(TRIM(CLEAN(C2)))

**bradvh0023** · 05-17-2011, 01:43 PM

Originally Posted by NBVC

Try:

=HYPERLINK(TRIM(CLEAN(C2)))

Ok, we are almost to the point of a perfect spreadsheet I believe; however, because I want to use mail-merge through Word, I would like to pull name,address, etc. from this excel spreadsheet. The links work great and appear as emails with the last formula, but there is still the "unknown" character appearing in front of all of the values in other columns. I'm not sure if you are using Excel '07, but if so I've attached a document with an example of what I am referring to. Let me know if you have any ideas. Much appreciated as always!

**NBVC** · 05-17-2011, 02:02 PM

The

=TRIM(CLEAN(cell)) formula will clean and trim the cell

or you can use vba code.. if you google something like "replace special characters excel"

Text to columns fixed width and delimited

LinkBack

Thread Tools

Rate This Thread

Display

Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited do not work HELP!

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Re: Text to columns fixed width and delimited

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions