CODEWITHSUNDEEP
excelexcel-formula
Amateur
Amateur

Reputation: 13

How to nest formula into one formula?

I have two formula's to extract only the last name, and a formula to get the correct character in capital.

i.e:

input: de heer K.W. in 't Bosch
output: in 't Bosch

=RIGHT(D11;LEN(D11)-FIND("|";SUBSTITUTE(D11;".";"|";LEN(D11)-LEN(SUBSTITUTE(D11;".";"")))))

The second formula gives:

input: de heer K.W. in 't Bosch
output: In 't Bosch  (as it should be).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(E8);" de ";" De ");" La ";" la ");" 'T ";" 't ") =

Is there any way to nest this in one formula? I've been trying for an hour, and I can't figure out why I can't get it done.

Upvotes: 1

Views: 64

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

It appears you define "last name" as everything after the last dot (.) So I would simplify the first formula to:

=TRIM(RIGHT(SUBSTITUTE(`D11`,".",REPT(" ",99)),99))

Then, to combine, merely substitute that for E8 in your second formula

 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)))," De "," de ")," La "," la ")," 'T "," 't ")

but I wonder if your de=>De is reversed? (Edit I just corrected that in my suggestion)

Note: my system uses the comma for the argument separator; you will need to change that to your semicolon

In O365, if you have the LET function, perhaps a more understandable formula:

=LET(x,TRIM(RIGHT(SUBSTITUTE(D11,".",REPT(" ",99)),99)),
     y, UPPER(LEFT(x)),
     z, MID(x,2,99),
     y&z)

Edit
Given the three examples you have mentioned, here is the output using either function, although with the "long" function I reverse "de" and "De" from what you show:

enter image description here

Upvotes: 1

Dominique
Dominique

Reputation: 17491

I tried combining both formulas and ended with this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(RIGHT(D11;LEN(D11)-FIND("|";SUBSTITUTE(D11;".";"|";LEN(D11)-LEN(SUBSTITUTE(D11;".";""))))));" de ";" De ");" La ";" la ");" 'T ";" 't ")

Does it do any good?

Upvotes: 0

Related Questions