parsing name in cell by comma?
 

Trying set up worksheets for use in access.

Sheet A has a list of doctors and their practices, i.e., Name= Smith, John
I., Practice= Anytown Pediatrics

Sheet B has a list of patients and their admitting doctor, but in this case,
it's Smith, John I (no period).

Trying to get to a result of associating the admitted patients to
appropriate practices. I think the best is to parse the MD cell by the comma
safter the last name but thought I would ask here for advice and assistance.

Can anyone help me out?
Thanks!!!!

parsing name in cell by comma?
 

If you want to parse that way, Data/ Text to Columns/ Delimited is probably
the best method.
--
David Biddulph

"Jon M" wrote in message
...
Trying set up worksheets for use in access.

Sheet A has a list of doctors and their practices, i.e., Name= Smith, John
I., Practice= Anytown Pediatrics

Sheet B has a list of patients and their admitting doctor, but in this
case,
it's Smith, John I (no period).

Trying to get to a result of associating the admitted patients to
appropriate practices. I think the best is to parse the MD cell by the
comma
safter the last name but thought I would ask here for advice and
assistance.

Can anyone help me out?
Thanks!!!!

parsing name in cell by comma?
 

Thanks!!!!

Now, what if I take the parsed names and put them back together in a uniform
fashion, i.e., Smith + John to Smith, John or John Smith?


"David Biddulph" wrote:

If you want to parse that way, Data/ Text to Columns/ Delimited is probably
the best method.
--
David Biddulph

"Jon M" wrote in message
...
Trying set up worksheets for use in access.

Sheet A has a list of doctors and their practices, i.e., Name= Smith, John
I., Practice= Anytown Pediatrics

Sheet B has a list of patients and their admitting doctor, but in this
case,
it's Smith, John I (no period).

Trying to get to a result of associating the admitted patients to
appropriate practices. I think the best is to parse the MD cell by the
comma
safter the last name but thought I would ask here for advice and
assistance.

Can anyone help me out?
Thanks!!!!

parsing name in cell by comma?
 

Hi,

What you want is call concatenation. Suppose the first name is in A1 and
the last name in B1 then to combine them use

=A1&" "&B1

As an aside you could avoid parsing the name.

=VLOOKUP(A1&"*",SheetA!A$1:B$100,2,)

This formula would go into SheetB and would retrieve the appropriate
practice assuming the data is in A1:B100, for example. Notice the first
argument A1 is on the patient sheet. &"*" is a wildcard meaning find the
name that starts with the value in A1 but can have anything after it, in this
case a period.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jon M" wrote:

Thanks!!!!

Now, what if I take the parsed names and put them back together in a uniform
fashion, i.e., Smith + John to Smith, John or John Smith?


"David Biddulph" wrote:

If you want to parse that way, Data/ Text to Columns/ Delimited is probably
the best method.
--
David Biddulph

"Jon M" wrote in message
...
Trying set up worksheets for use in access.

Sheet A has a list of doctors and their practices, i.e., Name= Smith, John
I., Practice= Anytown Pediatrics

Sheet B has a list of patients and their admitting doctor, but in this
case,
it's Smith, John I (no period).

Trying to get to a result of associating the admitted patients to
appropriate practices. I think the best is to parse the MD cell by the
comma
safter the last name but thought I would ask here for advice and
assistance.

Can anyone help me out?
Thanks!!!!