parsing name in cell by comma?

  • Thread starter Thread starter Jon M
  • Start date Start date
J

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!!!!
 
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?
 
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.
 
Back
Top