excel

%3CLINGO-SUB%20id%3D%22lingo-sub-834997%22%20slang%3D%22en-US%22%3Eexcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834997%22%20slang%3D%22en-US%22%3Eexample%20Ben%20Me%20mountain%20rd.%20Keene%20NH%2003431%20I%20have%20address%20all%20line%20up%20in%20one%20column%20one%20after%20another.%20I%20need%20them%20across%20the%20top%20so%20as%20to%20make%20a%20mail%20merge.%20I%20tried%20but%20lack%20the%20experience%20to%20do%20so.%20thank%20you%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-834997%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-835638%22%20slang%3D%22en-US%22%3ERe%3A%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-835638%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402787%22%20target%3D%22_blank%22%3E%40robbie2130%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyour%20question%20is%20not%20very%20clear.%20I%20think%20you%20have%20address%20information%20in%20one%20column%2C%20including%20name%2C%20street%2C%20town%2C%20zip%20code%2C%20and%20you%20want%20to%20split%20that%20data%20into%20separate%20columns.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20not%20an%20easy%20challenge%2C%20since%20names%20can%20have%20two%2C%20three%2C%20four%20words%2C%20streets%20can%20have%20several%20words%2C%20towns%20can%20have%20several%20words.%20So%2C%20in%20order%20to%20automate%20splitting%20the%20data%20with%20a%20formula%20or%20some%20logic%2C%20Excel%20needs%20to%20understand%20where%20the%20name%20ends%2C%20where%20the%20street%20starts%20and%20ends%2C%20where%20the%20town%20starts%20and%20ends%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJoe%20Bloggs%2C%20123%20ABC%20Street%2C%20Clearwater%2C%20WA%2C%201245%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20example%2C%20the%20comma%20separates%20the%20elements%20and%20can%20be%20used%20to%20determine%20the%20difference%20between%20name%2C%20street%2C%20town%2C%20state%20and%20zip%20code.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJoanna%20Sims%20Dark%20Grove%20Lake%20Weather%20CA%2001234%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20example%2C%20only%20the%20zip%20code%20and%20the%20state%20are%20clear.%20The%20rest%20cannot%20be%20clearly%20identified.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20your%20data%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836219%22%20slang%3D%22en-US%22%3ERe%3A%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836219%22%20slang%3D%22en-US%22%3EMy%20data%20looks%20like%20this%20example.%20Where%20there%20is%20one%20address%20after%20another%20down%20the%20column.%20Ron%20Robbins%2048%20Eaton%20RD.%20Swanzey%20NH%2003431%20No%20commas%20or%20periods%20needed%20I'm%20looking%20for%20this%20where%20each%20word%2Cetc.%20is%20spread%20out%20into%20there%20columns.%20col.%20col.%20col.%20col.%20col.%20col.%20Ron%20Robbins%2048%20eaton%20rd.%20Swanzey%20NH%2003446%20And%20I%20thank%20you%20for%20the%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837478%22%20slang%3D%22en-US%22%3ERe%3A%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402787%22%20target%3D%22_blank%22%3E%40robbie2130%3C%2FA%3E%26nbsp%3B%20just%20using%20a%20space%20as%20a%20separator%20to%20divide%20the%20data%20into%20columns%20will%20not%20give%20you%20good%20results%20if%20the%20data%20is%20not%20uniform.%20You%20can%20use%20Text%20to%20columns%2C%20but%20you%20may%20end%20up%20with%20a%20situation%20where%20you%20can't%20tell%20which%20column%20has%20the%20city.%20I%20color-coded%20name%2C%20address%2C%20city%2C%20state%20and%20zip%2C%20but%20how%20would%20Excel%20know%20which%20cell(s)%20to%20use%20for%20the%20city%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20939px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130151i9D406BDFD3B6A5A2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-05_09-15-01.png%22%20title%3D%222019-09-05_09-15-01.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840570%22%20slang%3D%22en-US%22%3ERe%3A%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840570%22%20slang%3D%22en-US%22%3EIf%20I%20mark%20each%20cell%20with%20example%3A%20first%20name......last%20name...%E2%80%A6.etc%2C....etc...%20etc...%E2%80%A6%20I%20been%20using%20text%202%20columns%20as%20you%20suggested%20with%20many%20different%20combinations%20but%20unable%20to%20put%20the%20data%20into%20there%20named%20columns.%20The%20first%20and%20last%20name%20goes%20into%20there%20mark%20columns%20but%20not%20the%20rest.%20It%20looks%20like%20the%20address%20has%20to%20be%20set%20up%20exactly%20a%20certain%20way%20before%20the%20delimited%20will%20read%20it%20correctly.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842997%22%20slang%3D%22en-US%22%3ERe%3A%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402787%22%20target%3D%22_blank%22%3E%40robbie2130%3C%2FA%3E%26nbsp%3B%20That's%20what%20I've%20been%20trying%20to%20tell%20you.%20How%20does%20Excel%20identify%20where%20the%20address%20ends%20and%20the%20city%20starts%3F%3F%3F%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20data%20needs%20to%20have%20some%20pointers%20that%20Excel%20can%20use%20as%20rules.%20What%20are%20these%20rules%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20your%20data%20is%20not%20suitable%20for%20this%20purpose%2C%20because%20it%20cannot%20be%20determined%20with%20logic%20how%20to%20split%20up%20the%20rows.%20Each%20row%20may%20be%20different%20and%20there%20are%20no%20pointers%20that%20Excel%20can%20use%20to%20split%20the%20data%20up%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20called%20%22garbage%20in%20-%20garbage%20out%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20a%20better%20result%2C%20you%20need%20better%20data%20first.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDon't%20shoot%20the%20messenger.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
robbie2130
New Contributor
example Ben Me mountain rd. Keene NH 03431 I have address all line up in one column one after another. I need them across the top so as to make a mail merge. I tried but lack the experience to do so. thank you
5 Replies

Hello @robbie2130,

 

your question is not very clear. I think you have address information in one column, including name, street, town, zip code, and you want to split that data into separate columns. 

 

This is not an easy challenge, since names can have two, three, four words, streets can have several words, towns can have several words. So, in order to automate splitting the data with a formula or some logic, Excel needs to understand where the name ends, where the street starts and ends, where the town starts and ends, etc.

 

Joe Bloggs, 123 ABC Street, Clearwater, WA, 1245

 

In this example, the comma separates the elements and can be used to determine the difference between name, street, town, state and zip code. 

 

Joanna Sims Dark Grove Lake Weather CA 01234

 

In this example, only the zip code and the state are clear. The rest cannot be clearly identified.

 

What is your data like?

My data looks like this example. Where there is one address after another down the column. Ron Robbins 48 Eaton RD. Swanzey NH 03431 No commas or periods needed I'm looking for this where each word,etc. is spread out into there columns. col. col. col. col. col. col. Ron Robbins 48 eaton rd. Swanzey NH 03446 And I thank you for the help

@robbie2130  just using a space as a separator to divide the data into columns will not give you good results if the data is not uniform. You can use Text to columns, but you may end up with a situation where you can't tell which column has the city. I color-coded name, address, city, state and zip, but how would Excel know which cell(s) to use for the city?

 

2019-09-05_09-15-01.png

If I mark each cell with example: first name......last name...….etc,....etc... etc...… I been using text 2 columns as you suggested with many different combinations but unable to put the data into there named columns. The first and last name goes into there mark columns but not the rest. It looks like the address has to be set up exactly a certain way before the delimited will read it correctly.

@robbie2130  That's what I've been trying to tell you. How does Excel identify where the address ends and the city starts????

 

The data needs to have some pointers that Excel can use as rules. What are these rules?

 

It looks like your data is not suitable for this purpose, because it cannot be determined with logic how to split up the rows. Each row may be different and there are no pointers that Excel can use to split the data up correctly.

 

This is called "garbage in - garbage out".

 

If you want a better result, you need better data first.

 

Don't shoot the messenger.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies