parsing name in cell by comma?

[Jon M]

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!!!!

 

[David Biddulph]

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

 

[Jon M]

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?

 

[Shane Devenshire]

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.