Split cell content in excel

[LaraC]

Hello all excel gurus. I would like to call on your expertise and ask for advice.

I have a column with the surname and initials (SURNAME ABC) of all our customers and would like to separate it into two columns.
I've found that it is not that easy because Excel doesn’t provide a function to do a reverse find for a character.
I prefer to do it without using VBA.

Any hints would be very helpful.

 

[Baxteen]

go to DATA tab
find TExt to colums.
use other delimiter, add a space.
boom.

 

[MickeyD]

LOL

The text to column function does not like Van der Merwe, etc!

 

[hellfire]

Hello all excel gurus. I would like to call on your expertise and ask for advice.

I have a column with the surname and initials (SURNAME ABC) of all our customers and would like to separate it into two columns.
I've found that it is not that easy because Excel doesn’t provide a function to do a reverse find for a character.
I prefer to do it without using VBA.

Any hints would be very helpful.

I'd use the Text-To-Columns feature - use the space between Surname and Initials as the delimiter

Edit: Yes, surnames like Van Der Merwe would be problematic

 

[froot]

I'd use the Text-To-Columns feature - use the space between Surname and Initials as the delimiter

Won't work if you have surnames containing spaces.

To find the index of the last space in a cell, use the following:

=SEARCH("@",SUBSTITUTE(AP5," ","@",LEN(AP5)-LEN(SUBSTITUTE(AP5," ",""))))

In "A BC" it will return 2.

 

[Necropolis]

Have a read through here:

https://support.office.com/en-us/ar...unctions-c2930414-9678-49d7-89bc-1bf66e219ea8

 

[TehStranger]

Hello all excel gurus. I would like to call on your expertise and ask for advice.

I have a column with the surname and initials (SURNAME ABC) of all our customers and would like to separate it into two columns.
I've found that it is not that easy because Excel doesn’t provide a function to do a reverse find for a character.
I prefer to do it without using VBA.

Any hints would be very helpful.

Assuming there is a space between the surname and initials:

Highlight the column of info
Go to the Data tab on the ribbon
Click "Text to column"
Make sure the "Delimited" radio button is selected, and click next
In the "Delimiters" checkbox group, make sure "Space" is selected
Click finish

Edit: If you've got surnames like "Van Wyk" etc, tough.

 

[LaraC]

Won't work if you have surnames containing spaces.

To find the index of the last space in a cell, use the following:

=SEARCH("@",SUBSTITUTE(AP5," ","@",LEN(AP5)-LEN(SUBSTITUTE(AP5," ",""))))

In "A BC" it will return 2.

It will return an error when the surname consist of more than one name.

 

[MickeyD]

Have a read through here:

https://support.office.com/en-us/ar...unctions-c2930414-9678-49d7-89bc-1bf66e219ea8

Interesting, thanks!

 

[froot]

It will return an error when the surname consist of more than one name.

Try it.

CCC d d

returns 6

"Van der merwe A" returns 14.

 

[LaraC]

Try it.


returns 6

"Van der merwe A" returns 14.

Wow! You've done it. Thank you very much.

 

[froot]

Wow! You've done it. Thank you very much.

Pleasure
All that's left is to take the right(text,len(text)-(<14>)) where 14 is the last index.

 

[LaraC]

This was how tried it but it failed when people had more than four initials.

=TRIM(MID(D2;FIND(" ";D2;LEN(D2)-4);10))