Conditional splitting of cells

P

Peter

I've run into a problem and would be really grateful for any help.

I bascially need to import data from a spreadsheet into our customer
database but I need to split the 'customer name' field into three
separate ones - Title (as applicable)/First Name/Last Name.

The problem is that there is no consistency in how this data has been
captured so some names contain their Title while others don't. I've
already found a macro that allows me to split out the last name into a
new column but I also need to identify Titles (hoping to being able to
identify Mr/Mrs/Ms/Dr) where they appear and move them as well. Can
anyone think of a macro that could do this?

Thanks again,
Peter
 
T

Tom Ogilvy

varr = Array("mr","mrs","ms","dr")
for i = lbound(varr) to ubound(varr)
iloc = instr(1,sCustomerName,varr(i),vbTextCompare)
if iloc <> 0 then
sTitle = varr(i)
exit for
end if
Next

Would be a start. You would also want to check that you found a title and
not a substring in the name (neDReck Smith for example)
 
Top