Forumula to Extract Data

K

Kip

I have an Excel spreadsheet with contact information I want to import
to a CRM lead system. Current Excel sheet format is all in one column
going down as follows: Company Name, Address, City State Zip (all one
field), Phone number, then empy cell then repeat of 4 cells listed
above. Want to convert to a new .csv file with listing going left to
right on on line as follows (ending up with 6 column fields - 1.
Company Name 2. Address 3. City 4. State 5. Zip 6. Phone

Any suggestions on how to accomplish this? Thanks.


Kip
 
G

Gord Dibben

The hard part will be parsing the column with City State Zip into three cells.

Any multiple word cities and/or states?

e.g.. New York New York 90210

Any delimiter besides space?

Post a sample of a few of these.


Gord Dibben MS Excel MVP
 
K

Kip

The hard part will be parsing the column with City State Zip into three cells.

Any multiple word cities and/or states?

e.g..   New York New York 90210

Any delimiter besides space?

Post a sample of a few of these.

Gord Dibben     MS Excel MVP







- Show quoted text -
Click to expand...

---------------

Hi Gord,

Thanks so much for looking at this. Below is an example of how the
format is currently laid out. On the City, State, Zip, don't know if
you can tell, but working backwards, from right to left, the zip is
always 5 digits, space before, 2 letter state abbreviation, then space
& comma and then random letter city designation. On the phone number
cell, always 2 numbers as formatted (only need it once). Thanks.

Kip


2. AA HEALTHCARE SERVICES INC
4415 NEWPORT WOODS ST
SAN ANTONIO, TX 78249
 (210)493-9473  (210)493-9473

3. AA MEDICAL INC
4814 HIGHWAY 78 SUITE 6
LILBURN, GA 30047
 (770)978-7933  (770)978-7933

4. AAA BEST CHOICE DIABETIC SUPPLY
6412 N UNIVERSITY DR STE 136
TAMARAC, FL 33321
 (954)718-3707  (954)718-3707

5. AAA GOLD CROSS MED SUPLY & HME CRE IN
1002 TRENTON AVE
POINT PLEASANT BEACH, NJ 08742
 (732)899-9300  (732)899-9300

6. AAA HOME HEALTHCARE
9001 AIRPORT BLVD STE 301
HOUSTON, TX 77061
 (713)910-0310  (713)910-0310
 
P

Pete_UK

Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
and put these formulae in the cells stated.

A1: =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))

B1: =INDIRECT("Sheet1!A"&(ROW(A1)*5-3))

C1: =LEFT(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)

D1: =MID(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)

E1: =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)

F1: =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-1)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-1))))

Then copy those formulae down as far as required (if you copy too far
you will get #VALUE errors).

Then you can fix the values in those columns, and then remove Sheet1.

Hope this helps.

Pete
 
K

Kip

Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
and put these formulae in the cells stated.

A1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))

B1:   =INDIRECT("Sheet1!A"&(ROW(A1)*5-3))

C1:   =LEFT(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)

D1:   =MID(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)

E1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)

F1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-1)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-1))))

Then copy those formulae down as far as required (if you copy too far
you will get #VALUE errors).

Then you can fix the values in those columns, and then remove Sheet1.

Hope this helps.

Pete










- Show quoted text -
Click to expand...

Hey Pete,

Thanks SO much! I would NEVER have been able to figure that out in a
million years! Thanks for your help..

Kip
 
K

Kip

Thanks for jumping in Pete.

Works like a charm.

Gord














- Show quoted text -
Click to expand...

Hey Gord,

Thanks so much for helping out....I'm inputting Pete's formulas
now....thanks again. I would have NEVER figured this out in a million
years. Thanks a bunch.

Kip
 
K

Kip

Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
and put these formulae in the cells stated.

A1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))

B1:   =INDIRECT("Sheet1!A"&(ROW(A1)*5-3))

C1:   =LEFT(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)

D1:   =MID(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)

E1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)

F1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-1)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-1))))

Then copy those formulae down as far as required (if you copy too far
you will get #VALUE errors).

Then you can fix the values in those columns, and then remove Sheet1.

Hope this helps.

Pete










- Show quoted text -
Click to expand...

Pete,

For the formula you show for Column F1 (phone number field) this
yields the phone number twice (if listed twice in souce field) or once
(if listed once in source field. I'm not sure if this is why, but when
I save as a .csv file, all the fields are comma delimited, EXCEPT the
phone number (which is the last item on each row) and when I try to
import into my CRM program, it gives me an error message and does not
import the phone numbers. Any ideas?

Kip
 
P

Pete_UK

In the data that you pasted earlier the first character of the phone
number block was character code 160, the non-breaking space character.
You also had a normal space character between the two phone numbers.
My formula looked for the normal space in the middle and then took the
characters to the right of that one.

So, if you now have a normal space at the beginning of that block then
the formula will take whatever is to the right of it, which means you
will get one or two phone numbers, depending on what you start with.
You will need to post some more examples of your data so that I can
investigate further.

It might be that your CRM program is expecting pure numbers to be
imported for the phone number and it will format them to show
parentheses and the dash, in which case these will need to be removed
(SUBSTITUTE can be used for this).

Hope this helps.

Pete
 
K

Kip

In the data that you pasted earlier the first character of the phone
number block was character code 160, the non-breaking space character.
You also had a normal space character between the two phone numbers.
My formula looked for the normal space in the middle and then took the
characters to the right of that one.

So, if you now have a normal space at the beginning of that block then
the formula will take whatever is to the right of it, which means you
will get one or two phone numbers, depending on what you start with.
You will need to post some more examples of your data so that I can
investigate further.

It might be that your CRM program is expecting pure numbers to be
imported for the phone number and it will format them to show
parentheses and the dash, in which case these will need to be removed
(SUBSTITUTE can be used for this).

Hope this helps.

Pete





- Show quoted text -
Click to expand...

Thanks Pete,

Think I've got it solved by using following: =right(Sheet1!A!, 13)
wich then gives me the phone number once.

I'm trying to sort by State field and cannot get this to function
properly and wondering if this is because of formula results being
displayed and not treating the result the same as text? Is there a way
to save the sheet with formula results that will then be "locked" so
that I can sort, edit, etc? When I save the sheet only, I get back
#REF! messages for all formula result fields. Thanks a bunch.

Kip
 
G

Gord Dibben

Copy>Paste Special>Values to get rid of formulas.

Then save.


Gord Dibben MS Excel MVP
 
K

Kip

Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
and put these formulae in the cells stated.

A1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))

B1:   =INDIRECT("Sheet1!A"&(ROW(A1)*5-3))

C1:   =LEFT(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)

D1:   =MID(INDIRECT("Sheet1!
A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)

E1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)

F1:   =RIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("Sheet1!
A"&(ROW(A1)*5-1)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-1))))

Then copy those formulae down as far as required (if you copy too far
you will get #VALUE errors).

Then you can fix the values in those columns, and then remove Sheet1.

Hope this helps.

Pete










- Show quoted text -
Click to expand...

Pete,

I am using these formulas on another sheet that is formatted
differently. So far, I think I've figured out how to change the
formula for the specific format of the new list, but in the formula
you've listed for cell A1, it cuts off the 1st letter (or digit) which
is PERFECT for the examples I gave you, but the new list I'm using
does not have a number before the name, so I want the entire field and
can't figure out what part of the formula to edit to chage that. Can
you advise what to drop from formula? Thanks.

Kip
 
K

Kip

Pete,

I am using these formulas on another sheet that is formatted
differently. So far, I think I've figured out how to change the
formula for the specific format of the new list, but in the formula
you've listed for cell A1, it cuts off the 1st letter (or digit) which
is PERFECT for the examples I gave you, but the new list I'm using
does not have a number before the name, so I want the entire field and
can't figure out what part of the formula to edit to chage that. Can
you advise what to drop from formula? Thanks.

Kip- Hide quoted text -

- Show quoted text -
Click to expand...

Pete,

Never mind on this. I think I figured it out by dropping the SEARCH
portion from the formula. Thanks.

Kip
 
P

Pete_UK

Pete,

Never mind on this. I think I figured it out by dropping the SEARCH
portion from the formula. Thanks.

Kip- Hide quoted text -

- Show quoted text -
Click to expand...

Well, you can also get rid of the RIGHT function in the formula,
leaving you with this:

=INDIRECT("Sheet1!A"&(ROW(A1)*5-4)

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Fomat Excel to .csv File 0
Mailing address block: sorting 1 row to columns 1
Expert Ron Needed.... 1
Move rows into columns 3
Collapsing Cells 3
Auto-fill from Validation List 2
Moving data from a string 1
extracting data from one format to a different format... 2

Top