Separate data in a cell

[Neilsen69]

"300 Flauger Hill Road - Aberdeen, OH 45101
"
This is my cell, I need to separate:

Street
City
State
Zip

into separate columns so I can import it into CRM
I can use the character right left to pull the zip and state because they have the same amount of characters, but it won't work for City and street.

Please help!

[NBVC]

If all entries are consistent, then assuming first entry is in A1, try these:

In B1:

=LEFT(A1,FIND("-",A1)-1)

In C1:

=TRIM(MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1))

in D1:

=LEFT(RIGHT(A1,8),2)

in E1:

=RIGHT(A1,5)

each copied down

[Neilsen69]

If all entries are consistent, then assuming first entry is in A1, try these:

In B1:

=LEFT(A1,FIND("-",A1)-1)

In C1:

=TRIM(MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1))

in D1:

=LEFT(RIGHT(A1,8),2)

in E1:

=RIGHT(A1,5)

each copied down

First, thank you for helping me with this. Okay B1 & C1 worked great, D1 and E1 did not work, possibly there maybe space characters in between. How can I check that. Weird I thought those would be the easier of the two. I can always just make a OH column. But I do need to grab those Zip codes.

[NBVC]

How about:

=LEFT(RIGHT(TRIM(A1),8),2)

and

=RIGHT(TRIM(A1),5)

[Neilsen69]

How about:

=LEFT(RIGHT(TRIM(A1),8),2)

and

=RIGHT(TRIM(A1),5)

D1 pulled just the letter H and E1 pulls nothing, just empty. I appreciate your help, this is beyond my skill set, but I hope to reverse engineer your formula to learn it

[NBVC]

How about?

=LEFT(TRIM(MID(A1,FIND(",",A1)+1,100)),2)

and

=RIGHT(TRIM(A1),5)

[Neilsen69]

How about?

=LEFT(TRIM(MID(A1,FIND(",",A1)+1,100)),2)

and

=RIGHT(TRIM(A1),5)

D1 is good

E1 still nothing but D1 is solved

I am so sorry about this

[Neilsen69]

Okay I gave you the master so you can see what I am starting with, and a workbook to show your formulas. E1 worked on just row 1. Again thank you, does this site have any excel training courses so I can get better at this?

[NBVC]

So the last 5 characters in the original are not the zip code digits? Even after trimming?

Can you show sample where it didn't work?

[NBVC]

You have a special non-printable carriage return character at the end...

try:
=RIGHT(CLEAN(A1),5)

[Neilsen69]

How about?

=LEFT(TRIM(MID(A1,FIND(",",A1)+1,100)),2)

and

=RIGHT(TRIM(A1),5)

You have a special non-printable carriage return character at the end...

try:
=RIGHT(CLEAN(A1),5)

You're awesome thanks, don't know how you know this stuff, I am truly envious. Thank You will mark solved.