CODEWITHSUNDEEP
excelexcel-formulavba
Grafit
Grafit

Reputation: 699

Split prefix and surname (based on array data)

I have a column of data existing out of names, which may or may not contain a surname prefix. Those prefixes can exist out of multiple words. I have a list of all possible prefixes, but now I need to split the prefix and surname and make 2 columns with the data.

What I did was writing an excel formula like the following:

=IF(
   RIGHT(A1;7) = " van de"
 ;
   RIGHT(A1;6)
 ;
   IF(
     RIGHT(A1;4) = " van"
   ;
     RIGHT(A1;3)
   ;
     IF(
       RIGHT(A1;3) = " de"
     ;
       RIGHT(A1;2)
     ;
       --Insert more nested If statements here--
     )
   )
 )

Data of the surnames can look like the following:

Name1 van de
Name1 van
Name1
Name1 Name2 van
Name1-Name2 Name3 van de
Name1 Name2 Name3

What I want:

  OriginalName   |  Name  |  Prefix
-----------------|--------|----------
      a b        |   a    |    b
      a b c      |   a    |    b c

Firstly this is a pretty inefficient method, but I automated the creating of this formula, so that wasn't a problem anymore. Now I found out there's a limit to the nested If statements one can have, and I have to exceed that limit.

How should I solve this problem?

I have an array with the possible prefixes. Maybe this will help?

Upvotes: 2

Views: 404

Answers (3)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

I know this has already been answered but I done this yesterday and didn't have time to submit it (had to run for the bus).

All Prefix values have a space before them...

enter image description here

Column B formula (Array formula - Ctrl+Shift+Enter in formula bar) =INDEX(SUBSTITUTE(A1,Prefix,""),MATCH(SMALL(LEN(SUBSTITUTE(A1,Prefix,"")),1),LEN(SUBSTITUTE(A1,Prefix,"")),0))

Column C formula - =TRIM(SUBSTITUTE(A1,B1,""))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

Put your list in order of the longest surname to the shortest. I put mine in E1:E3.

Then use this array formula:

=TRIM(IFERROR(SUBSTITUTE(A1,INDEX($E$1:$E$3,MATCH(TRUE,ISNUMBER(SEARCH($E$1:$E$3,A1)),0)),""),A1))

Then to get the Surname:

=IFERROR(INDEX($E$1:$E$3,MATCH(TRUE,ISNUMBER(SEARCH($E$1:$E$3,A1)),0)),"")

Being an array formulas they need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Upvotes: 2

UndeadBob
UndeadBob

Reputation: 1129

I made the assumption that you wanted to separate the "van" and "de" prefixes from the rest of the name. If I misunderstood, please provide more examples of your problem/question...

The following solution requires a helper column to determine where the "Prefix" starts, but you can hide it if necessary:

enter image description here

First, put my values in A8:A9 (van; de) anywhere and name it prefix so it can be referenced in the following formulas.

The formula in C1 is an array formula (use Ctrl+Shift+Enter):

=MIN(IF(ISNUMBER(SEARCH(prefix,A1)),SEARCH(prefix,A1)))

The formula in D1 and E1 or normal formulas:

=IF(C1>0,LEFT(A1,C1-2),A1)
=IF(C1>0,MID(A1,C1,LEN(A1)),"")

Upvotes: 2

Related Questions