Insert maiden name into fullname 7

[mars19301]

Is there a cell formula that will insert colum A = "Doe" into "Mary A. K. Smith" so the result is Mary A. K. Doe Smith"?

Thank you in advance for any heko,

 

[SkipVought]

Hi,

You have to know with certainty that the value will be inserted in the string, at the point where the LAST SPACE occurs. BUT, do you have any last names like
[tt]
Van Der Voort
St John
[/tt]
or do you have sufix values like
[tt]
Jr
II
III
[/tt]
It would be better and easier if you were to split the current column into First (including initials) and Last and then, after you're sure that that is correct, conactenate all three.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mars19301]

I am certain the insert will be in the last space. I'm only dealing with 500 names so I will manually adjust the very few exceptiosn. The suffix is in a separate column so I'll add that afterwards, but the cell formula is stumping me. Ideas?

Thank you for your help.

 

[xlbo]

Probaly doable in formula but easier in a bit of code as a function:

Function Ins_Maiden(fName As String, mName As String)
Dim lSpace As Integer
lSpace = InStrRev(fName, " ")
blah = Left(fName, lSpace) & mName & " " & Right(fName, Len(fName) - lSpace)
End Function

Copy the code into a module

If you have the initial name in A1 and the MAiden name in B1 then in C1, enter
=Ins_Maiden(A1,B1)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mars19301]

Thank you, XLBO, but I've never worked with code in Excel although I'm willing to learn. Can you point me to a resource? m

 

[SkipVought]

alt+F11 will toggle between the sheet and the VB Editor.

In the VB editor [/b]Insert > Module[/b]

Paste xlbo's code into the Module.

Toggle back to your sheet and use the function that you pasted in the module, just as you would any other spreadsheet function packaged with Excel, as he demonstrated, like =Ins_Maiden(A1,B1).

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[xlbo]

as per Skip's response ;-)

As I said, this can be done in a formula but would need a bunch of testing to find the last space - VBA has the instrrev function which is very handy for finding items from the back of a string!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Let's say your maiden names are in column A and your full names (minus the maiden name) are in column B.

First, you'll need to know how many spaces are in your cell so you can insert at the last space. For Mary A. K. Smith,
[tab]=LEN(B1) - LEN(SUBSTITUTE(B1, " ", ""))
will return 3

Now we want to take everything to the left of the last space, add in the maiden name found in column A, and finish up with everything to the right of the last space.

We know there are three spaces, but we need to find where that last space is within the whole cell. In order to find the last space, I'll use Substitute to change the last space to something else - some character(s) that will NEVER appear in the data. Two "pipes" in a row should be safe. The pipe is the shifted character on the key above [Enter] - the same key as the slash (\). The last argument of Substitute is instance_num - we'll use our first formula to return how many spaces there are.

For "Mary A. K. Smith" in column B,
[tab]=SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", "")))
will return "Mary A. K.||Smith"

Now we have a string that we can easily divide into first name/initials vs. last name with simple Left/Right formulas.

[tab]=LEFT(B1, FIND("||", SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", "")))))
will return "Mary A. K."

Add the maiden name using an &, like this:
[tab]=LEFT(B1, FIND("||", SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", ""))))) & A1
to get "Mary A. K. Doe"

and now finish off by getting everything to the right of the ||:
[tab]=RIGHT(B1, LEN(B1) - FIND("||", SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", "")))) + 1)
will return " Smith" - the "+1" at the end including the leading space.

Now just tie everything together and you come up with this (admittedly long) formula:
[COLOR=blue white]=LEFT(B1, FIND("||", SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", ""))))) & A1 & RIGHT(B1, LEN(B1) - FIND("||", SUBSTITUTE(B1, " ", "||", LEN(B1) - LEN(SUBSTITUTE(B1, " ", "")))) + 1)[/color]
which returns "Mary A. K. Doe Smith"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

- exactly the reason I went for a code solution John !! Maybe I'm getting lazy in my old age ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi mars19301:

Following is another formula based solution ...

=REPLACE($A1,LOOKUP(30,FIND(" ",$A1,ROW($1:$30)))+1,0,$B1& " ")

cell A1 houses the original name entry, cell B1 houses the maiden name to be added, resulting entry in cell C1 incorporates the maiden name.

I have assumed that the last space in the given entry will be no more than 30 characters away from start. In case you do have a much longer name, increase 30 to suit.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi mars19301:

Following is an illustration of the use of the formula in my preceding post.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

nice Yogi! ==> *

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[yogia]

Hi Skip:

Thanks for being the source of inspiration on this board and instilling the spirit of helping ... Thank You Thank You!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

I'm still trying to figure out how it works.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[AnotherHiggins]

You ain't kidding, Skip. I saw that formula and immediately suspected that it worked via the PFM principle.

Have a purple pointy thing on my, too, yogia.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Hi Skip:

so let me disect my formula ...

=REPLACE($A1,LOOKUP(30,FIND(" ",$A1,ROW($1:$30)))+1,0,$B1& " ")

using

FIND(" ",$A1,Row($1:$30)

I am locating the blank character in string in A1 starting from positions 1 to 30. I chose 30 arbitrarily to be comfortably large enough for my last " " (blank) in string in A1 -- and thus I generate an array of numbers depicting the postion of the " " (blank) starting from position 1 in the string.

then

LOOKUP(30,FIND(" ",$A1,ROW($1:$30))

locates for me the largest number in the array generated in the preceding step. I chose to LOOKUP 30 because that is by far the largest number I will be looking for in the string -- so this gives me the position of the last " " (blank) in string in A1

then using

REPLACE($A1,LOOKUP(30,FIND(" ",$A1,ROW($1:$30)))+1,0,$B1& " ")

I replace the character in the position last " " (blank) plus 1 with the maiden name and a " " (blank) character.

That's it!



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

You ain't kidding, Skip. I saw that formula and immediately suspected that it worked via the PFM principle.

Hi John:

Need some education here ... what is the PFM Principal?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

Since Tek-Tips is a family-friendly site, let's just say "Pure Freaking Magic".

I learned the term while majoring in physics during college. It is used when you can't describe what is going on. An example of usage: "First, an electron is fired into the device, where the PFM Principle is applied. And, of course, an apple comes out the other end."

I can see how the formula works, but I never would have thought to do it that way. Very clever.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Got It ... Thanks John!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Our Yogi is not like those OTHER Yogis!

Better than the average Tek-Tipper!
It's not over, 'til...!


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}