• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text Functions

J

Joeman187

Member
Hi Chandoo People


I am wondering if I can do something in the Excel text function. I can extract my middle name using this formula: =MID(I54,FIND(" ",I54)+1,FIND(" ",I54,FIND(" ",I54)+1)-1-FIND(" ",I54)). Not a problem in doing that. What I would like to do is within that formula can I change my middle name to a nickname. Can I use a substitute or replace formula within that formula to change the middle name? Many thanks for your assistance. Joe from Michigan.
 
Probably. It would help if you gave us more of an example of what you want. Perhaps some before/after examples?
 
Hi, Thanks for responding. Here is what I am trying to do:


Joseph Lee Horling Lee =MID(I54,FIND(" ",I54)+1,FIND(" ",I54,FIND(" ",I54)+1)-1-FIND(" ",I54)) Thats where the Lee pops up next to my full name after doing the formula. Then I would like to change the Lee in that same cell to Joeman. Any thoughts? Joe.
 
Hi, Joeman187!

Try this:

=SUSTITUIR(I54;EXTRAE(I54;ENCONTRAR(" ";I54)+1;ENCONTRAR(" ";I54;ENCONTRAR(" ";I54)+1)-1-ENCONTRAR(" ";I54));"Wilbur") -----> in english: =SUBSTITUTE(I54,MID(I54,FIND(" ",I54)+1,FIND(" ",I54,FIND(" ",I54)+1)-1-FIND(" ",I54)),"Wilbur")

Just in case your nick shouldn't be Wilbur, please update formula properly.

Regards!
 
@SirJB7

Welcome back my friend. Hope you enjoyed your holiday.


As for the problem, if we don't really care what the previous middle name was, you can use:

=TRIM(REPLACE(SUBSTITUTE(A2," ",REPT(" ",999)),999,999,"Joeman"))


which is a bit shorter and takes fewer arguments. Note that 999 is just an arbitrary large number.
 
@Luke M

Hi, my friend!

Thanks for the welcome back, it seems as if a 2 weeks holiday became a 5 weeks sabbatical period... and I want more!!!

About your solution, following Old Chippy's goals, it's true that it's short. But, as b(ut)ob(ut)hc uses to say, remember that I'm lazy and even more after doing nothing computer related since last day of last november, so I simply took Joeman187's formula and embedded it into a new one. Was I supposed to optimize it? I've never been formally notified, so as my lawyer hates to hear when he says "Objection", "Denied".

Regards!

PS: More than lazy I'm still wondering what do my fingers have to do with this thing called keyboard...
 
Hi all,


Thanks for responding. I will try all your information. Thanks for the help. I'm sure I will be asking more questions since I've been reviewing Excel on my own. Thanks again.
 
Hi, Joeman187!

If I were you I'd try with Luke M's solution, it's more energetically efficient.

Regards!
 
You must log in or register to reply here.
Back
Top