LinkBack URL
About LinkBacks
Looking for a formula to remove First letter with dot in from it before the actual first name. This formulais closest I could get but not working as desired
Please Login or Register to view this content.
Sample data format:
ThanksHTML Code:
Looking for a formula to remove First letter with dot in from it before the actual first name. This formulais closest I could get but not working as desiredPlease Login or Register to view this content.
Sample data format:
ThanksHTML Code:
Last edited by bjnockle; 02-01-2020 at 12:38 PM.
The results you show are not consistent
The first returns Rudolph ( where does that come from?)
The two last return the first letter and the dot you want to remove?
As Pepe has said your desired result does not match your description, but this will do what you have described.
=TRIM(REPLACE(A2,1,2,""))
I have corrected the mistakes
R. Tudolkh = Tudolkh (desired result)
A. Gonna = Gonna (desired result)
J. Robin = Robin (desired result)
A. J = A. J (desired result)
B.R. = B.R. (desired result)
Fluff13: Your proposed formula is not working as desired. It is removing the first letter from names with a letter and a dot before it. For example, John. This John does not have a letter and a dot before it and should only return John instead of removing John's first letter (Ohn).
R. Tudolkh = Tudolkh (desired result)
A. Gonna = Gonna (desired result)
J. Robin = Robin (desired result)
A. J = A. J (desired result)
B.R. = B.R. (desired result)
John = John (desired result)
Thanks
Last edited by bjnockle; 02-01-2020 at 12:31 PM.
You haven't given an example like John in your sample - none of us is psychic.
I think you'd better provide a more detailed set of sample data.
EDIT: Please do not post-edit opening posts with extra details. You have made it look as if Fluff missed a requirement, when in fact the requirement had not been stated. That's bad form. Add new details in new posts, please.
Last edited by AliGW; 02-01-2020 at 12:34 PM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
AliGW: Yes, just realized that. Sorry.
Thanks.
Please read my edit to post #6 and take note. Thanks.
AliGW: Noted. Uploaded sample file.
Thanks.
If it's returning ohn for John, then you have a space before John, which is something else that you didn't mention.
Fluff13: Here is the sample book. Sorry for the confusion.
Assuming that the result in B3 is wrong, try
=TRIM(IF(OR(LEN(A2)<=5,MID(A2,2,1)<>"."),A2,REPLACE(A2,1,2,"")))
Fluff13: Excellent formula. Working as desired. You are simply the best. Will mark thread closed and post a new one for removing letters after first name.
You're welcome & thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks