I have a problem a client sent me an excel doc with names and addresses

in Column E the header is city/state/zip

i would like to break that out into 3 seperate columns city in E, state in F and zip in G

heres an example how the orginal data came in ColumnE
Goldendale, WA, 98620-

Does anyone have a quick way to do this.

thakns

If the data is consistent then you should be easily able to split into 3 columns. using Search, mid left len and right functions.

Or you could lookup split and merge cells in help to see if that applies

Brian

Or you could lookup split and merge cells in help and see if that applies

Brian

I do quite a lot of these and put Excel into Access and the result back to Excel but I am sure it could be done in Excel.

The first 3 expressions split it and [abc] is the name of the field with the full address.

Exp1: Left([abc],InStr([abc],",")-1)

Exp2: LTrim(Mid([abc],InStr([abc]," "),InStrRev([abc]," ")-InStr([abc]," ")-2))

Exp3: LTrim(Mid([abc],InStrRev([abc]," "),Len([abc])-InStrRev([abc]," ")))

Exp4 below would remove the - at the end and then the full address would be Goldendale, WA, 98620 and you would apply the Mid, Right etc to that address

Exp4: Replace([abc],"-","")

IIF can also be used so as to group different entry types. As Brian said "If the data is consistent then you should be easily able to split into 3 columns" but if is not then you need to break up the records. You can really waste away some time on this sort of thing. :D

This should be quite trivial with a vba approach, as long as the data is consistent.


sub blah()

for each rng in worksheets("SheetName").range("E2:E" & worksheets("SheetName").range("E65536").end(xlup).row)

vArr = split(rng.value, ", ")


rng.value =vArr(0)
rng.offset(0,1) = vArr(1)
rng.offset(0,2) = vArr(2)

next rng

end sub

This should be quite trivial with a vba approach, as long as the data is consistent.



Actually, you can have quite a lot of variation in the data. And I would suggest queries in Access are the way to go.

As a side note you will find in the real world that fixing data is not trivial. For example, I can pay $650 per 1000 or $1500 for the same 1000. I buy the 650/1000 because I can fix it.............but it needs more than a trivial approach.

[/B]

Actually, you can have quite a lot of variation in the data. And I would suggest queries in Access are the way to go.

If you've got a lot of variation in the data how is a query going to help?

As a side note you will find in the real world that fixing data is not trivial. For example, I can pay $650 per 1000 or $1500 for the same 1000. I buy the 650/1000 because I can fix it.............but it needs more than a trivial approach.

The OP's problem has a trvial answer in vba.

What is the above example meant to mean? It makes no sense.

PS

When I say Access queries are best........they can need some help.

That help is using a macro that resets the value of the base data (feeds back results) and runs across the record....and then goes to next record and repeats.

If you saw some of the data we buy........but I really like doing it and I get the bonus in cost saving which is big, at least in my world. Fucked up data arranement costs me $6500 for 10000.....if I could not fix the data it would be $15,000......It usually takes about 3 days to fix the data and do what has to be done to bring it into the data base.

But nothing trivial:)

If you've got a lot of variation in the data how is a query going to help?

Are you serious?

PS

When I say Access queries are best........they can need some help.

That help is using a macro that resets the value of the base data (feeds back results) and runs across the record....and then goes to next record and repeats.

If you saw some of the data we buy........but I really like doing it and I get the bonus in cost saving which is big, at least in my world. Fucked up data arranement costs me $6500 for 10000.....if I could not fix the data it would be $15,000......It usually takes about 3 days to fix the data and do what has to be done to bring it into the data base.

But nothing trivial:)

Still don't see what this has to do with the OPs data. From the info he supplied there is a delimiter in the text that makes splitting it out into different columns trivial. In this case moving it from excel to access and back to excel is a waste of time.

I never said what you did was trivial just the OP's requirements based on the info supplied.


Are you serious?


It would help if I knew what in particular you were talking about.

Why doesn't rkrause come back and say thanks chergh all done and dusted, then the thread could close.

Brian

chergh

I will bet his data varies. The solutions I gave him can be different if zip code always has 5 numbers and American states have two letters.......I am in Australia and not sure.

The solutions I gave him only require that he can make a calculated field in a query and he can just paste in what I gave him. After he does that there will be few wrong results and he will explain some of his data variations.:)

And you ask how queries can handle data variations.........lots of ways.....for example, the solution I posted allows variations in spacing......an IIF can do wonders

But as Brian said the thread starter will just paste your stuff in and Presto:D

The OP's problem has a trvial answer in vba.

What is the above example meant to mean? It makes no sense.

We buy listings for telemarketing, mail out etc

At 65c each the data is a mess. At $1.50 each the data is cleaned up. I can do the fixing myself and so buy the listings at 65c and also fix listings for others......in other words they buy the 65c and I fix their listing so their total cost will be around $1.10-$1.20, cheaper than $1.50 and my list clean up is better than the $1.50

chergh

I will bet his data varies. The solutions I gave him can be different if zip code always has 5 numbers and American states have two letters.......I am in Australia and not sure.

The solutions I gave him only require that he can make a calculated field in a query and he can just paste in what I gave him. After he does that there will be few wrong results and he will explain some of his data variations.:)

And you ask how queries can handle data variations.........lots of ways.....for example, the solution I posted allows variations in spacing......an IIF can do wonders

But as Brian said the thread starter will just paste your stuff in and Presto:D

I would still consider a query the wrong tool for what the OP wants.

While I might use queries in some situations if I want to handle varying data like UK post codes I'd be much more likely to reach for a tool like regular expressions.