Forumula to Extract Data

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



0
hmkipp (12)
11/23/2010 6:39:44 PM
excel 39879 articles. 2 followers. Follow

15 Replies
716 Views

Similar Articles

[PageSpeed] 29

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

On Tue, 23 Nov 2010 10:39:44 -0800 (PST), Kip <hmkipp@gmail.com> wrote:

>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
>
>
0
phnorton (279)
11/23/2010 9:21:28 PM
On Nov 23, 1:21=A0pm, Gord Dibben <phnor...@shaw.ca> wrote:
> The hard part will be parsing the column with City State Zip into three c=
ells.
>
> Any multiple word cities and/or states?
>
> e.g.. =A0 New York New York 90210
>
> Any delimiter besides space?
>
> Post a sample of a few of these.
>
> Gord Dibben =A0 =A0 MS Excel MVP
>
>
>
> On Tue, 23 Nov 2010 10:39:44 -0800 (PST), Kip <hmk...@gmail.com> wrote:
> >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- Hide quoted text -
>
> - Show quoted text -

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

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
=A0(210)493-9473=A0 (210)493-9473

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

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

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

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

0
hmkipp (12)
11/30/2010 10:23:38 PM
Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
and put these formulae in the cells stated.

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

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

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

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

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

F1:   =3DRIGHT(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

On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
> ---------------
>
> 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
> =A0(210)493-9473=A0 (210)493-9473
>
> 3. AA MEDICAL INC
> 4814 HIGHWAY 78 SUITE 6
> LILBURN, GA 30047
> =A0(770)978-7933=A0 (770)978-7933
>
> 4. AAA BEST CHOICE DIABETIC SUPPLY
> 6412 N UNIVERSITY DR STE 136
> TAMARAC, FL 33321
> =A0(954)718-3707=A0 (954)718-3707
>
> 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> 1002 TRENTON AVE
> POINT PLEASANT BEACH, NJ 08742
> =A0(732)899-9300=A0 (732)899-9300
>
> 6. AAA HOME HEALTHCARE
> 9001 AIRPORT BLVD STE 301
> HOUSTON, TX 77061
> =A0(713)910-0310=A0 (713)910-0310- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
12/1/2010 1:18:57 AM
Thanks for jumping in Pete.

Works like a charm.


Gord

On Tue, 30 Nov 2010 17:18:57 -0800 (PST), Pete_UK <pashurst@auditel.net> wrote:

>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
>
>On Nov 30, 10:23�pm, Kip <hmk...@gmail.com> wrote:
>> ---------------
>>
>> 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- Hide quoted text -
>>
>> - Show quoted text -
0
phnorton (279)
12/1/2010 5:52:54 PM
On Nov 30, 5:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> and put these formulae in the cells stated.
>
> A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> D1: =A0 =3DMID(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> F1: =A0 =3DRIGHT(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
>
> On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
>
>
>
> > ---------------
>
> > 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
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=
=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(2=
10)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> > 3. AA MEDICAL INC
> > 4814 HIGHWAY 78 SUITE 6
> > LILBURN, GA 30047
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=
=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
70)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> > 4. AAA BEST CHOICE DIABETIC SUPPLY
> > 6412 N UNIVERSITY DR STE 136
> > TAMARAC, FL 33321
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=
=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(9=
54)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> > 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> > 1002 TRENTON AVE
> > POINT PLEASANT BEACH, NJ 08742
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=
=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
32)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> > 6. AAA HOME HEALTHCARE
> > 9001 AIRPORT BLVD STE 301
> > HOUSTON, TX 77061
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=
=A0=A0=A0=A0=A0(713)910-0310=A0 (713)910-0310- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Hey Pete,

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

Kip
0
hmkipp (12)
12/2/2010 6:45:52 PM
On Dec 1, 9:52=A0am, Gord Dibben <phnor...@shaw.ca> wrote:
> Thanks for jumping in Pete.
>
> Works like a charm.
>
> Gord
>
>
>
> On Tue, 30 Nov 2010 17:18:57 -0800 (PST), Pete_UK <pashu...@auditel.net> =
wrote:
> >Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> >and put these formulae in the cells stated.
>
> >A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1=
!
> >A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> >B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> >C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> >A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> >D1: =A0 =3DMID(INDIRECT("Sheet1!
> >A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> >E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> >F1: =A0 =3DRIGHT(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
>
> >On Nov 30, 10:23 pm, Kip <hmk...@gmail.com> wrote:
> >> ---------------
>
> >> 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
> >>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=
=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-94=
73=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> >> 3. AA MEDICAL INC
> >> 4814 HIGHWAY 78 SUITE 6
> >> LILBURN, GA 30047
> >>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=
=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-79=
33=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> >> 4. AAA BEST CHOICE DIABETIC SUPPLY
> >> 6412 N UNIVERSITY DR STE 136
> >> TAMARAC, FL 33321
> >>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=
=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-37=
07=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> >> 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> >> 1002 TRENTON AVE
> >> POINT PLEASANT BEACH, NJ 08742
> >>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=
=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-93=
00=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> >> 6. AAA HOME HEALTHCARE
> >> 9001 AIRPORT BLVD STE 301
> >> HOUSTON, TX 77061
> >>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=A0=A0=
=A0=A0=A0(713)910-0310(713)910-0310- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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
0
hmkipp (12)
12/2/2010 6:46:45 PM
You're welcome, Kip - thanks for feeding back.

Pete

On Dec 2, 6:45=A0pm, Kip <hmk...@gmail.com> wrote:
>
> Hey Pete,
>
> Thanks SO much! I would NEVER have been able to figure that out in a
> million years! Thanks for your help..
>
> Kip
0
pashurst (2576)
12/2/2010 7:36:45 PM
On Nov 30, 5:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> and put these formulae in the cells stated.
>
> A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> D1: =A0 =3DMID(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> F1: =A0 =3DRIGHT(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
>
> On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
>
>
>
> > ---------------
>
> > 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
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=
=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(2=
10)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> > 3. AA MEDICAL INC
> > 4814 HIGHWAY 78 SUITE 6
> > LILBURN, GA 30047
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=
=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
70)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> > 4. AAA BEST CHOICE DIABETIC SUPPLY
> > 6412 N UNIVERSITY DR STE 136
> > TAMARAC, FL 33321
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=
=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(9=
54)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> > 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> > 1002 TRENTON AVE
> > POINT PLEASANT BEACH, NJ 08742
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=
=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
32)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> > 6. AAA HOME HEALTHCARE
> > 9001 AIRPORT BLVD STE 301
> > HOUSTON, TX 77061
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=
=A0=A0=A0=A0=A0(713)910-0310=A0 (713)910-0310- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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
0
hmkipp (12)
12/6/2010 10:36:55 PM
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

On Dec 6, 10:36=A0pm, Kip <hmk...@gmail.com> wrote:
>
> 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
0
pashurst (2576)
12/7/2010 1:55:56 AM
On Dec 6, 5:55=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Dec 6, 10:36=A0pm, Kip <hmk...@gmail.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

Thanks Pete,

Think I've got it solved by using following:   =3Dright(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
0
hmkipp (12)
12/8/2010 8:19:30 PM
Copy>Paste Special>Values to get rid of formulas.

Then save.


Gord Dibben     MS Excel MVP

On Wed, 8 Dec 2010 12:19:30 -0800 (PST), Kip <hmkipp@gmail.com> wrote:

>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.
0
phnorton (279)
12/8/2010 9:07:14 PM
On Dec 8, 1:07=A0pm, Gord Dibben <phnor...@shaw.ca> wrote:
> Copy>Paste Special>Values to get rid of formulas.
>
> Then save.
>
> Gord Dibben =A0 =A0 MS Excel MVP
>
>
>
> On Wed, 8 Dec 2010 12:19:30 -0800 (PST), Kip <hmk...@gmail.com> wrote:
> >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.- Hide quot=
ed text -
>
> - Show quoted text -

Thanks, Gord! Will do....

Kip
0
hmkipp (12)
12/8/2010 9:08:48 PM
On Nov 30, 5:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> and put these formulae in the cells stated.
>
> A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> D1: =A0 =3DMID(INDIRECT("Sheet1!
> A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> F1: =A0 =3DRIGHT(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
>
> On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
>
>
>
> > ---------------
>
> > 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
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=
=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(2=
10)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> > 3. AA MEDICAL INC
> > 4814 HIGHWAY 78 SUITE 6
> > LILBURN, GA 30047
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=
=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
70)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> > 4. AAA BEST CHOICE DIABETIC SUPPLY
> > 6412 N UNIVERSITY DR STE 136
> > TAMARAC, FL 33321
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=
=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(9=
54)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> > 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> > 1002 TRENTON AVE
> > POINT PLEASANT BEACH, NJ 08742
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=
=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(7=
32)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> > 6. AAA HOME HEALTHCARE
> > 9001 AIRPORT BLVD STE 301
> > HOUSTON, TX 77061
> >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=
=A0=A0=A0=A0=A0(713)910-0310=A0 (713)910-0310- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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
0
hmkipp (12)
12/8/2010 10:30:39 PM
On Dec 8, 2:30=A0pm, Kip <hmk...@gmail.com> wrote:
> On Nov 30, 5:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
>
>
> > Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> > and put these formulae in the cells stated.
>
> > A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("Sheet=
1!
> > A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> > B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> > C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> > A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> > D1: =A0 =3DMID(INDIRECT("Sheet1!
> > A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> > E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> > F1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("Sheet=
1!
> > 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
>
> > On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
>
> > > ---------------
>
> > > 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 spac=
e
> > > & 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
> > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-947=
3=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=
=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> > > 3. AA MEDICAL INC
> > > 4814 HIGHWAY 78 SUITE 6
> > > LILBURN, GA 30047
> > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-793=
3=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=
=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> > > 4. AAA BEST CHOICE DIABETIC SUPPLY
> > > 6412 N UNIVERSITY DR STE 136
> > > TAMARAC, FL 33321
> > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-370=
7=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=
=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> > > 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> > > 1002 TRENTON AVE
> > > POINT PLEASANT BEACH, NJ 08742
> > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-930=
0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=
=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> > > 6. AAA HOME HEALTHCARE
> > > 9001 AIRPORT BLVD STE 301
> > > HOUSTON, TX 77061
> > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-031=
0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0 (713)910-0310- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> 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 -

Pete,

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

Kip
0
hmkipp (12)
12/8/2010 11:56:24 PM
On Dec 8, 11:56=A0pm, Kip <hmk...@gmail.com> wrote:
> On Dec 8, 2:30=A0pm, Kip <hmk...@gmail.com> wrote:
>
>
>
>
>
> > On Nov 30, 5:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
>
> > > Assume your blocks of data start in A1 on Sheet1. Insert a new Sheet
> > > and put these formulae in the cells stated.
>
> > > A1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-4)),LEN(INDIRECT("She=
et1!
> > > A"&(ROW(A1)*5-4)))-SEARCH(" ",INDIRECT("Sheet1!A"&(ROW(A1)*5-4))))
>
> > > B1: =A0 =3DINDIRECT("Sheet1!A"&(ROW(A1)*5-3))
>
> > > C1: =A0 =3DLEFT(INDIRECT("Sheet1!
> > > A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))-1)
>
> > > D1: =A0 =3DMID(INDIRECT("Sheet1!
> > > A"&(ROW(A1)*5-2)),SEARCH(",",INDIRECT("Sheet1!A"&(ROW(A1)*5-2)))+2,2)
>
> > > E1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-2)),5)
>
> > > F1: =A0 =3DRIGHT(INDIRECT("Sheet1!A"&(ROW(A1)*5-1)),LEN(INDIRECT("She=
et1!
> > > 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
>
> > > On Nov 30, 10:23=A0pm, Kip <hmk...@gmail.com> wrote:
>
> > > > ---------------
>
> > > > 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 i=
f
> > > > you can tell, but working backwards, from right to left, the zip is
> > > > always 5 digits, space before, 2 letter state abbreviation, then sp=
ace
> > > > & comma and then random letter city designation. On the phone numbe=
r
> > > > 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
> > > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-=
9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=
=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473=A0=A0=A0=A0=A0=A0=A0=A0(210)493-9473
>
> > > > 3. AA MEDICAL INC
> > > > 4814 HIGHWAY 78 SUITE 6
> > > > LILBURN, GA 30047
> > > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-=
7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=
=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933=A0=A0=A0=A0=A0=A0=A0=A0(770)978-7933
>
> > > > 4. AAA BEST CHOICE DIABETIC SUPPLY
> > > > 6412 N UNIVERSITY DR STE 136
> > > > TAMARAC, FL 33321
> > > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-=
3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=
=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707=A0=A0=A0=A0=A0=A0=A0=A0(954)718-3707
>
> > > > 5. AAA GOLD CROSS MED SUPLY & HME CRE IN
> > > > 1002 TRENTON AVE
> > > > POINT PLEASANT BEACH, NJ 08742
> > > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-=
9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=
=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300=A0=A0=A0=A0=A0=A0=A0=A0(732)899-9300
>
> > > > 6. AAA HOME HEALTHCARE
> > > > 9001 AIRPORT BLVD STE 301
> > > > HOUSTON, TX 77061
> > > >=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0(713)910-=
0310=A0=A0=A0=A0=A0=A0=A0=A0(713)910-0310=A0 (713)910-0310- Hide quoted tex=
t -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -
>
> > 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 -
>
> 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 -

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

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

Hope this helps.

Pete
0
pashurst (2576)
12/9/2010 1:51:05 AM
Reply:

Similar Artilces:

Help with data not getting plotted
The following quote is posted on behalf of a client using Windows and Office 2003 Standard. Please email responses directly to me, too, since I do not read this group much. Any help/insight would be most appreciated. I'll do my best to relay any questions people have and bring back any answers to said questions. <quote> The Goal: In an effort to come up with a new way of looking at organizational structure, I employed Excel's 'Donut Chart' function. My goal was to have the head of the organization in the middle of the chart and then that person's direct re...

data/validation/list size
XL2000 data/validation/list size here does matter my screen size that I am working with is at 50 percent and the list box is to small to see any suggestions besides resizing my screen size back to 100 percent. Also is this message is in plain text? Yes, it's plain text. (Thanks.) And that's the only solution I know. Although Debra Dalgleish shows away to change it via a change in selection in an event procedure: http://www.contextures.com/xlDataVal08.html#Larger ~Alan wrote: > > XL2000 > > data/validation/list size h...

change the display data format cell already contain data as date
In Excel sheet a column contains datas as like 19.12.2008 this column contains"General" format. Now I want to change and display above as date format like 12/19/2008 Hi May this can help press ctrl and F then click replace and Type FIND WHAT . in Replace with type / then click replace all "Harun" wrote: > In Excel sheet a column contains datas as like 19.12.2008 this column > contains"General" format. Now I want to change and display above as date > format like 12/19/2008 You can use the 'Convert Text to Columns Wizard to ...

Data Migration Framework 02-17-05
Where do I download Data Migration Framework? It should have come in one of your CDs that came with CRM..... -Gary "David" <dduncan@tsi-omis.123com> wrote in message news:eJ53gjQFFHA.3384@tk2msftngp13.phx.gbl... > Where do I download Data Migration Framework? > > If you have access to MDSN, you could also download it there. Kind regards, Ronald Lemmen Avanade Netherlands "Gary" wrote: > It should have come in one of your CDs that came with CRM..... > > -Gary > > "David" <dduncan@tsi-omis.123com> wrote in message &...

Pivot tables remembers old data values
I created a pivot table with a variable called "description" placed in the ROW layout. I deleted the source data and put in new data that has different values for the description variable but when I "Refresh data" the pivot table remembers the old description values. When I click on the arrow down button of the pivot all the old description values along with the new values appear. How can I refresh the pivot table without keeping the old values. Thanks J Hi have a look at http://www.contextures.com/xlPivot04.html -- Regards Frank Kabel Frankfurt, Germany &qu...

How can you set a cells data using a formula?
I would like to set a cells data using a formula... is this possible? For example is there a function to set cell A1 to have the value "Bob" =set(a1,"bob") =A1="Bob" -- Regards, Peo Sjoblom "Moloth" <Moloth@discussions.microsoft.com> wrote in message news:677E8F28-06E8-462F-919F-52EE5A1EAC9C@microsoft.com... >I would like to set a cells data using a formula... is this possible? > > For example is there a function to set cell A1 to have the value "Bob" > > =set(a1,"bob") Note that a function can only r...

data labels change format
I have an Excel chart with data labels at the top of each series bar. The label is foramtted in Arial font 10point and bold. When I update the data and and refresh the chart, the data lables change to Times new Roman and 12 pt font. Is there a way to lock in the font format as a default? Thanks Hi, Any chance your chart is actual a pivot chart? If so check this KB article. http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 Cheers Andy john mcmichael wrote: > I have an Excel chart with data labels at the top of each series bar. The > label is foramtted in Arial ...

Extract part of a cell value
I have a column of numbers (each of 8 digits) and i want to extract i another column only the 3rd and 4th digits (eg 12345678 - shoul produce 34). Can anyone give me some code to help me -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26247 Assuming your numbers are in column A, in cell B2 enter the formul =MID(A1,3,2) and copy down the column. This assumes that you alway want the 3rd and 4th digets. ...

import data from Netscape Comm 4.79 to Outlook 2003
I have tried to import my Nescape mail and address book through outlook using the "import and export wizard"("import internet mail and addresses"/"outlook import tool"). However there is no Netscape option available in this window. Netscape is installed on the computer. How do I load the converter into the 'outlook import tool' window? Try an export to Outlook Express and then import into Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal...

Adding an additional forumula to my SUMPRODUCT formula
Good afternoon everyone I am sure this is pretty simple but everything I am trying isn't working, Hoping someone can help I have the following formula =IF(SUMPRODUCT(($C69=1)*($C90<L$3)),L69,"") Works prefectly, But I want to add to the start of it IF B2="EX","" otherwise run the sumproduct formula above many thanks Try.. =IF(B2="EX","",<your formula>) 'or will this do? =IF(AND(B2="EX",C69=1,C90<L3),L69,"") -- Jacob (MVP - Excel) "Mark D" wrote: >...

Extracting hyperlink data from XML file
Hi I need to extract the data from the following XML file (fragment) which consists of and order number (in this case 415045) and some related part numbers into a .NET dataset. The part number data is contained in other parts of the file which are referenced by links to named anchors eg I need to extract the 'id190' from this line of code: <ns1829687187:Part href="#id190"/>. How do I do this please? Am new to XML so any help much appreciated. Jonathan Attree <multiRef id="id141" soapenc:root="0" soapenv:encodingStyle="http://schemas.x...

using data from one worksheet in another worksheet
I have 2 wksheets that have a cell that has a total sum of expenses for that year at the bottom. I then have another worksheet that I'm trying to take the totals (which are using the sum formula for that worksheet) of those worksheets and places it on the this sheet. I have tried the formula of using the = then clicking on the cell of the other wksheets but I get an amt of $0.00 which isn't true. I think it's because the totals on the other wksheets are using the sum formula. Could use any help with this. Much appreciated. Hi J. Please refer to:- http://ww...

Extract comments?
I would like to take a column of cells, each with a comment attached to it, and be able to produce two columns, whereby the first column contains the original contents, and the second column the texts of the comments. From what I can find on the Net, it seems like there are no built-in functions for doing that (e.g. "Extract Comment"), but rather it must be done with VBA. Can anybody enlighten? Thanks! Saved from a previous post: You can retrieve the text from a comment with a userdefined function like: Option Explicit Function GetComment(FCell As Range) As Variant Ap...

Importing data 02-24-08
I have data from one access DB that has an autonumber Primary Key. It's been exported to Excel so that it can be transferred to another Access DB that has the same table and the same autonumber PK. When I try to import into the second DB, I get an error message about the duplicate PKs. I tried to export all the data without the primary key, thinking that it would auto assign a PK when imported to the second DB, but received an error there as well. What is the best way around this problem? Any assistance - as soon as possible -would be appreciated. Your approach, of exporting w...

Text to Columns / Importing Data
I frequently import data to Excel from ShowcaseStrategy - an SQL database - or from as400 queries. There are instances when Excel won't recognize the exact same data. For example, if in cell A1 I type "Dog" and I import the text "Dog" into cell B1, and in cell C1 I enter the function =IF(A1=B1,"Match","Non-Match"), the resulting value in C1 would be "Non-Match". (This is a simplified example. I am usually using VLOOKUP to pull matching records from tens of thousands of imported records to an existing table, and Excel won't rec...

Reading Last Row of Data
Is this possible? I have an excel document with two worksheets in it. Worksheet #2 is just a bunch of data sorted by date. Worksheet #1 displays data from the last row of worksheet #2. Is it possible to have excel detect the last row of data on Worksheet #2 and display it on Worksheet #1? Right now I am manually changing the cell references on worksheet #1 as I add data in worksheet #2. Any help would be greatly appreciated! -- sslack ------------------------------------------------------------------------ sslack's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Adding data to a list
I have a list of data that I want to add to or subtract from and I would like to be able to click on a button which would bring up a text box where I could insert the new data then sort the new list. Thanks, Mike Take a look at data>form -- Regards, Peo Sjoblom "Mike" <mike@ehb-docks.fsbusiness.co.uk> wrote in message news:bultp0$8ee$1@news8.svr.pol.co.uk... > I have a list of data that I want to add to or subtract from and I would > like to be able to click on a button which would bring up a text box where I > could insert the new data then sort the ne...

data splitting
hello.. I have a column of data which i want to split into 2 other columns. For example, A B C ABC SCH, CDE SCH ABC SCH CDE SCH FGH SCH, FGH SCH - IJK SCH, LMN SCH IJK SCH LMN SCH As seen above, the data is separated by commas. I have input this formuale into cell B1 : =LEFT(A1,FIND(",",A1)-1) and I manage to get the "ABC SCH". But I don't know what formuale to put in cell C1. Can anyone help me ...

Change format of chart data values
My chart displays values as 123.22 instead of 123. I have edited the datasheet, and the query, but everytime I open the form it reverts back to 123.22 Would like to know how fix this 've tried creating with insert chart and pivotchart wiz, I've been able to edit the values but it doesn't save my changes. Any help would be greatly appreciated. ...

Synchronizing Outlook Data
Hi, I'm reposting my question; hopefully someone will have a response that will work. I'm trying to synch my notebook PC with my desktop PC to share Outlook information. Is it possible to do this from within Outlook without buying a third-party addon? I have two copies of Outlook 2002, one on my desktop and the other on my notebook PC. I need to be able to use the calander, tasks, contacts, etc on both PCs and I cannot find any information on how to do this. Did I waste my money in buying another copy of Outlook 2002 to run on my desktop? Any help would be greatly appre...

Real time data in Visio
Hi My doubt is that I have a visio app in which I am trying to view real time data. It is working fine. But in VisioViewer, this data is not getting updated. If i am using Visio for my HMI application , then i need to port my application in number of stations from where i can monitor the real time data. I thought VisioViewer will provide the run time version facility to see the real time data without the need of loading Visio. This is similar to .NET or VB, where i create a setup, and run it on a syatem which does not have .NEt studio installed. The application works fine. But my doubt is why ...

Showing a forumula
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C58E2E.019CA4D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to show a formula just for one column only? Thanks alot in advance for your help. Julia ------=_NextPart_000_000A_01C58E2E.019CA4D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"te...

Usage data
I am interested in developing an application that monitors data usage. For example, I need to know how many rows that a user accesses from specific tables; maybe also, what columns they access. This data should probably be gathered at the data-access layer because it would be more certain and not make me rely on having to parse ad-hoc queries. Does SQL provide any sort of monitoring tool that can give this data? Suggestions? Thanks, Howard SQL 2008 has a lot of features and built in reports... also maybe performance dash board? (It's a seperate installation package) ...

How do I plot data in Excel that is captured on separate sheets; .
Data is saved in Excel worksheets by date. I need to plot various data from each of the separate sheets on a summary sheet. That is, the summary sheet plots data across multiple worksheets. For example, Avg1 is on Sheet1, Avg2 on Sheet2, ... Then, on the summary sheet, I would like to plot the Avg1, Avg2, ... sequence. I have not found a simple way to do this. ...

extracting cells (words) from excel column
I have a huge list of words in a txt file.. i transfered them to a exce spreadsheet. all words are on there own line. i need to extract all words which have "apple" in the word. I tryed going through the sorting mechanism of excel but cannot seem t find the right function. I thought "text to column" was the right on but found you can't enter specific words. rya -- Message posted from http://www.ExcelForum.com Hi Ryan, Introduce a helper column. If your data is in Col A, then Col B should be something like =IF(ISERROR(FIND("apple",A1)),"","...