I have Extracted names in column A.
DinethPrabash
JohnSmith
WilliamTell
RobinHood
All follow this rule (no three names etc)
I want to replace second capital letter with space (or any other delimiter) and that letter.
e.g. Dineth Prabash
John Smith
So I can use text to column and make it two columns (first name, last name)
can you help me?
better if no macro.
Here!
=REPLACE(A1,LOOKUP(2,FIND(UPPER(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A"&LEN(A1)))),0," ")
Cheers!
Deep Dave
=TRIM(REPLACE(A1,MIN(INDEX(FIND(CHAR(ROW($65:$90)),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ",2),0)),," "))
Try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
both are great. thanks.
Hi,
Inspired from nflsales' solution, here is another one..
=REPLACE(A1,LOOKUP(10^99,FIND(CHAR(ROW(65:90)),A1,2),FIND(CHAR(ROW(65:90)),A1,2)),0," ")
OK, can anybody show me how these work? I am like blind here.
Alright!
=CHAR(ROW(65:90)) This piece generates Capital A - Z (Select the Formula in the Formula Bar and press F9 to see..) Press Ctrl+Z to undo later on.
Then we use Find which is case sensitive to find any Capital Letter between A-Z starting from the 2nd Character (Very Important, as there can only be 1 cap letter after the 2nd character). The resulting array returns the character position.
Then we use the char position with the replace function.. When 0 is used for the 3rd Argument of the Replace function, it actually inserts and not replaces the specified character which we define in the 4th argument, in our case a Space.
And then you have your answer..
Last edited by NeedForExcel; 09-10-2015 at 12:30 AM.
Sir your formula works well except for this word AliAhmed It NA
This thread is eight years old ...
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks