Need Help arraanging data in one cell into three different cells based on values.

[danpotash]

cell 1A Brian Ward 3179LOVERS GAP RD VANSANT VA 24656 us
cell 2A 434 8215935 REGGIE D POWELL 13260 WARDS RD LYNCHBURG VA 24501 us
cell 3A DARYL BIECIRER 7762 WILD NEST LN DUBUQUE lA 52003 us

Here is an example of what is being copied into excel. I would like to arrange each cell into 3 columns name adress and phone number
the problem is it is not so easy as changing text to columns because formatting of everything would be a night mare. I was looking at the
data and noticed that if some code could be written which can check each value in the cell to see if it is a number or text it could help me in solving my issue.
for example

cell 1A if first data values in column are numbers place in phone column if not place in Name colum so the code would see brian ward becuase the first value is text and then the second it sees a number place the rest of data in adress column

i hope you see where I am going with this I have 90000 columns of data to do and manually it would be a nightmare
I would greatly appreciate any help
thanks
daniel

[Jakobshavn]

The following macro should work if the address field begins with a number:

Please Login or Register  to view this content.

[danpotash]

jakob thanks for reply i apologize for not being so familiar but i catch on to things quick can you please guide me on where to copy that code?

[Jakobshavn]

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Macros must be enabled for this to work!

I have attached a working sample.

[danpotash]

Macro.xlsm

i followed all steps an upon running the macro it said subscript out of range can i will attach the worksheet

[Jakobshavn]

Sorry...............you need to select the sheet with the data before you run the macro.

I ran the code on your sheet and have attached the result. Running the code has revealed a problem:

The first address has no space between the number and the rest of the address. Records like this should be fixed before the macro is run.

[danpotash]

thank you so so so much for your help

[Jakobshavn]

Thanks for the feedback!

[danpotash]

so far soo good the prior code worked phenomenally for the data , my next set of docs are listed in this format shown below
can you show me how to adjust this specific format ?

ATI: BRADLEY COCHRAN 200 MEDICAL CENTER DR SW FORT PAYNE Al 256-997-2189 (256) 845-8901
ROBERT CASTER 620 CRAGIN PARK DR LAS VEGAS NV (702) 877-3297
LAREY l CROSS 7806 BENT TREE AMARILLO TX (806) 352-8384
RICHARD A WOOD 42357 50TH ST W 101 KEELER CA (661) 943-4118 (661) 943-7101
ERIK H MALPICA 145 TAMBOER DR HALEDON NJ 973-238-1880
CARLOS WAGNER 14 WALLST FL 20 NEW YORK NY
STEPHAN W SMITH 4338 AIKEN DR WARRENTON VA (540) 347-4551 540-347-7255
JOHN HUNT 1725 RENEE LN POPLAR BLUFF MO (573) 785-9446
GARY KIRKLAND 4620 PORTOLA RD ATASCADERO CA 805) 466-2742
LAWRENCE GRATKINS 1302 FRANKLIN AVE STE 2800 NORMAL ll (309) 268-2727
ATIN RICHARD LINDERMANIS 1023 POTOMAC DR HOUSTON TX 713-461-5601
DOMENIC L GROSSO 20825 CIPRES WAY BOCA RATON Fl (561) 852-1595
Attn: Armin H. Wittenberg 166 North Canon Drive BEVERLY HILLS CA (310) 275-3229
Attn: Dan Richter 17500 Gillette Avenue IRVINE CA (714) 495-4275

[Jakobshavn]

This format is much more difficult.

I need to figure out where in the string the phone number begins!

(not all phone numbers begin with an open parens)

[danpotash]

can we some how manipulate the code to go backwards what i mean by that is as follows each string ends with a number per say
ATI: BRADLEY COCHRAN 200 MEDICAL CENTER DR SW FORT PAYNE Al 256-997-2189 (256) 845-8901

"going over the string" from the end until u "meet" a letter and then u have your phone number

[Jakobshavn]

Great idea!

I will not be able to resume work on this until Sat.AM, but I think you have found the solution.

[danpotash]

Not a problem i will look forward to it on saturday again thank you sooo soo much for your help and g-d bless you.

[danpotash]

looking at the second set of data, it appears that the phone numbers appear after a state code. try building a reference table for state codes, then use a vlookup function to find "CA " for California, then use the mid function with that location to split your phone number/numbers. take note that some rows seem to have multiple phone numbers which you may need to split further.

[jindon]

This should do what you want.

Please Login or Register  to view this content.

[danpotash]

so far so good all the code is working my final question is I need to now separate the address field into address city state zip and country can you please help me with the remainder of the code?

[Jakobshavn]

I am working the issue.

[danpotash]

again here is the format of the address field
(as you can see some have zips some don't some have country some don't to me it is irrelevant I don't need code to determine country or zip I just need code to separate address field)
200 MEDICAL CENTER DR SW FORT PAYNE Al
3045 ROSECRANS ST STE 214 SAN DIEGO CA 92110 us
620 CRAGIN PARK DR LAS VEGAS NV
7806 BENT TREE AMARILLO TX
42357 50TH ST W 101 KEELER CA
145 TAMBOER DR HALEDON NJ
14 WALLST FL 20 NEW YORK NY
4338 AIKEN DR WARRENTON VA
1725 RENEE LN POPLAR BLUFF MO
4620 PORTOLA RD ATASCADERO CA
1302 FRANKLIN AVE STE 2800 NORMAL ll
1023 POTOMAC DR HOUSTON TX
20825 CIPRES WAY BOCA RATON Fl
166 North Canon Drive BEVERLY HILLS CA
17500 Gillette Avenue IRVINE CA
813 SHADES CREEl< PKWY STE 1008 BIHMINGHAM AL 35209 us
4521 S HULEN ST STE 102 FORT WORTH TX 76109 us
3045 ROSECRANS ST STE 214 SAN DIEGO CA 92110 us
14 Wall Street NEW YORK CITY NY 10005 us
21708 SE 245TH ST MAPLE VALLEY WA 98038 us
9509 KEY WEST AVE ROCKVILLE MD 20850 us
780 MARSH RD PLAINWELL Ml 49080 us
14 Wall Street NEW YORK CITY NY 10005 us
18 STONEY HILL ROAD BROOKSIDE NJ 7926 us
830 LAFAYETIE RD BRYN MAWR PA 19010 us
4716 Torphln Hill Ct NAPERVILLE IL 60564 us
3250 32ND ST LONG ISLAND CITY NY 11106 us
600 E CHANDLER BLVD CHANDLER AZ 85225 us
14 WALL ST FL23 NEW YORK CITY NY 10005 us
14 Wall Street NEW YORK CITY NY 10005 us
43139 BLACK BAYOU RD GONZALES LA 70737 us
21622 MARGUEniTE PKWY APT 600 MISSION VIEJO CA 92692 us
11319 FEATHERSTAR LN HOUSTON TX 77067 us
4205 ROLLING MEADOWS PL NORMAN OK 73072 us

[jindon]

This should do most of the cases...

Please Login or Register  to view this content.

[danpotash]

Thank you jindon for your help unfortunately the one constant that i was able to find that work is extracting all the numbers from the string (the zip column did not always result with the actual zip ) but with in the results there is a column that takes all the numbers from the string and arranges them together the good thing about that is that i can use a simple right function to return the last 5 digits in the string to come up with the actual zip. with that being said here is my thought process and let me know if we can put it in code.

Step 1 remove all numbers from string ex: 20438 196th ave se renton wa 98058 this ultimately yeilding the result of 2043819698058
Step 2 use right function to extract last five digits of prior result ex: 95058
Step 3 Extract the result of step 2 from origingal string ex: 95058 extracted from the original string would yeild20438 196th ave se renton Wa us
Step 4 combine if and right function together basically if last 2 characters of string are us then extract from string if not leave alone.
Step 5 new result would be 20438 196th ave se Renoton WA and run a function to return all character 2 spaces starting from the end (returning WA and Renton) ( I understand that some citys have 2 words even 3 words ) but i think if i can get those results i might be able to do the rest manually

[jindon]

I can only work with the data you have provided.