Normally this wouldn't be a problem if the database had correct delimiters, BUT I am running into a problem. Help?
In one cell I have CITY ST ZIP
Just like that... No comma, one space in between each one. I have seperated the state and zip, but the city is posing a problem, because some cities have two words in their name.
I HAVE been able to make ST and ZIP uniform, to where there is two characters and then a space and then 5 characters.
So I need to count backward 9 characters and take everything BEFORE those 9 characters. I am a newb to excel. I figured out the LEFT and RIGHT, but I need the OPPOSITE of left and right.
Please help.
Example data:
<table x:str="" style="border-collapse: collapse; width: 200pt;" border="0" cellpadding="0" cellspacing="0" width="267"><col style="width: 200pt;" width="267"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 200pt;" height="17" width="267">DURHAM NC 27717</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">WATERTOWN CT 67950</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ROCKINGHAM NC 28379</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">NORTH MYRTLE BEACH SC 29582</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">SUMMERFIELD NC 27358</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ABINGDON VA 24210</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">WHITSETT NC 27377</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GOLDSBORO NC 27533</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">TANNERSVILLE PA 18372</td> </tr> </tbody></table>
In one cell I have CITY ST ZIP
Just like that... No comma, one space in between each one. I have seperated the state and zip, but the city is posing a problem, because some cities have two words in their name.
I HAVE been able to make ST and ZIP uniform, to where there is two characters and then a space and then 5 characters.
So I need to count backward 9 characters and take everything BEFORE those 9 characters. I am a newb to excel. I figured out the LEFT and RIGHT, but I need the OPPOSITE of left and right.
Please help.
Example data:
<table x:str="" style="border-collapse: collapse; width: 200pt;" border="0" cellpadding="0" cellspacing="0" width="267"><col style="width: 200pt;" width="267"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 200pt;" height="17" width="267">DURHAM NC 27717</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">WATERTOWN CT 67950</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ROCKINGHAM NC 28379</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">NORTH MYRTLE BEACH SC 29582</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">SUMMERFIELD NC 27358</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ABINGDON VA 24210</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">WHITSETT NC 27377</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">GOLDSBORO NC 27533</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">TANNERSVILLE PA 18372</td> </tr> </tbody></table>
