Splitting data in a field

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I have names in a field called NAME_LFM. The names in the field are
seperated using a #.

WHITE#JANET#R
VEGA SR#MANUEL#H
HUGHES#LB
MOORE#LLOYD#RICHARD
METCALF#CRAIG#H

How can I change these to be formatted like
WHITE, JANET R
VEGA SR, MANUEL H
HUGHES, LB
MOORE, LLOYD RICHARD
METCALF, CRAIG H

Or split then in to two fields last name and first_middle both with no
# in them.

Thanks
 
To split into two fields, use:

Left(NAME_LFM, InStr(NAME_LFM, "#") - 1)
Replace(Mid(NAME_LFM, InStr(NAME_LFM, "#") + 1), "#", " ")

To change the existing field, try:

Left(NAME_LFM, InStr(NAME_LFM, "#") - 1) & ", " &
Replace(Mid(NAME_LFM, InStr(NAME_LFM, "#") + 1), "#", " ")
 
Back
Top