• 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.

Separating Names

T

Tripp

Member
Hey there,

I currently have a spreadsheet with lots of information relating to clients but the way the information is imported to excel (CSV), all the names are presented together in one column (e.g. Mr joe Bloggs all in one cell). I've played around with the CSV input but it seems the names were never separated correctly so I was wondering if it is possible to create a macro that will add a new column next to the name column and separate the cell into (title and first name) and (surname). There is a space in between each of names in the cell.

I have a basic understanding of VBA but I dont know how to search for the second space and cut and paste into the new cell.

Any help appreciated,

Tripp
 
This can be done via Formula if the layout / structure of the names are consistent

Can you post a sample file ?
 
Hey Hui,

Really, I thought all the separating, copying and pasting would require a macro but that sounds good.

I have attached a sample with just some names in the format I receive them.

Regards,

Tripp
 

Attachments

  • Passenger Sample.xlsx
    11.1 KB · Views: 8
Hi,


As per your attached


In B2 = TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)*2))

In C2 = TRIM(SUBSTITUTE(A2,B2,""))


Then drag them down..
 
B2: =MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)
C2: =RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2)+1))

copy both cells down

Note that A6 and A11 have multiple names and will need to be separated manually
 
Thanks very much guys!

The solution I have used for the moment is Asheesh's method for column B and Hui's for column C. Once I work through the formulas and fully understand them I will get back to you on my final solution.

Once again thank you very much, so glad I didn't have to use a macro's as in my case, they always seem to break down when lots of people use them across multiple computers :)

Regards,

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