LinkBack URL
About LinkBacks
Hi,
I have a load of data extracted into a tab and they extract as e.g "john.smith". I need a forumla or a way to to show either "Smith, John" or "John Smith". Can anyone please advise?
Cheers
Mass changing upper case to lower case letters
Hi,
I have a load of data extracted into a tab and they extract as e.g "john.smith". I need a forumla or a way to to show either "Smith, John" or "John Smith". Can anyone please advise?
Cheers
Last edited by jonathan.haynes; 09-03-2012 at 08:03 AM.
hI try this
Please Login or Register to view this content.
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Hi,
Sorry this makes no sense what so ever, please can you explain?
Hi Jonathan,
Tom's way would be the cleanest way of doing it. If you are not happy with using VBA then there are formulas that can do it as I have written below.
The easiest way to do it would be to first split it into 2 columns. To do this click on the column the names are in and then click on 'Data' and 'Text to Columns'. Select Delimited and click next. Now select 'other' and put a . in the box. Now click next and then finish.
This should seperate all the cells into 2.
Say the 2 columns are A and B. You now need the formula
and then drag the formula down.Please Login or Register to view this content.
The last code you would need isThis would end up with 4 columns of data but at least the problem is sorted.Please Login or Register to view this content.
I would suggest trying the VBA way first as a basic knowledge of VBA is always useful.
Hope this helped.
Danny
if your name is in a1
=PROPER(SUBSTITUTE(A1,"."," "))
for a formula solution
Josie
if at first you don't succeed try doing it the way your wife told you to
what exactly makes no sense and what should be explain? I do not follow (this code makes what You asked for)
All,
I don't know how to use VBA, I need formulas I can use in excel only.Originally Posted by tom1977
This doesn't work? It is coming up as 0 after the formula is complete.
This looks good however I cant use 4 columns!Hi Jonathan,
Tom's way would be the cleanest way of doing it. If you are not happy with using VBA then there are formulas that can do it as I have written below.
The easiest way to do it would be to first split it into 2 columns. To do this click on the column the names are in and then click on 'Data' and 'Text to Columns'. Select Delimited and click next. Now select 'other' and put a . in the box. Now click next and then finish.
This should seperate all the cells into 2.
Say the 2 columns are A and B. You now need the formula
and then drag the formula down.Please Login or Register to view this content.
The last code you would need isThis would end up with 4 columns of data but at least the problem is sorted.Please Login or Register to view this content.
I would suggest trying the VBA way first as a basic knowledge of VBA is always useful
Hope this helped.
Danny
-
I apologise for being a pain!
what's in a1? if it's "john.smith" as you said then you should get "John Smith" back.
In my example, this is the formula I have:
=PROPER(SUBSTITUTE(FM5,jonathan.haynes,Haynes, Jonathan))
I need an instance number otherwise the forumla isnt complete?
why do you have that? use the formula I suggested and change the cell reference-and nothing else-to match your data
Hi Joseph,
Can you see the attached file so maybe you can see where I am going wrong?
the formula is what I posted
=PROPER(SUBSTITUTE(A1,"."," "))
nothing else! ;-)
That makes sense, I somehow thought that the " was supposed to be replaced by the name!
Is there any way we can get the name by surname then forename? e.g Haynes, Jonathan?
Cheers!
yes
=PROPER(MID(A1&", "&A1,FIND(".",A1)+1,LEN(A1)+1))
Thats great! Thanks.
Two more questions - How about if I have two names in the extract and it comes out like this:
jonathan.haynes; john.smith
I need it to say: Haynes, Jonathan; Smith, John
Also
A few of the extracted names have middle names e.g joe.h.bloggs, this is making the names come out as H.Bloggs, Joe
See next post for correct attachment
Cheers!
Last edited by jonathan.haynes; 09-03-2012 at 10:33 AM.
Incorrect attachment! Use this one:Example 2.xlsx
how many exceptions are there? can you correct them manually? a formula to handle these possibilities would get pretty complicated I reckon
There is around over 200, which isn't great!
ok I've added a udf to your file that should work with the examples given
Thats great thanks - however how do I go about getting this across to my own Spreadsheet? I can get as far as opening VB.
Cheers
copy the code from module1 into a new module in your real workbook
Again excellent help, one last problem! See attached!
CheersExample 22.xlsm
change convertname to
Please Login or Register to view this content.
Nothing seemed to have changed, can you double check on my example?
Cheers
you have to edit one of the formulas for the change to take effect (just f2 and enter one of them)
Example 22.xlsm
That makes perfect sense, thank you so much for the help!
So my final question is, if I have two or more names in different cells and need to go into the same cell on a different tab how would I go about doing that?
See attached for example.
Cheers
and I thought we'd already had "one last problem"
since the names are in yet another format this is a different question really
I also thought we had one last question but another problem always pops up.
And ok well happy to make a new thread if you can't assist here.
Cheers for the help
@ jonathan.haynes
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks