Separate real name and Parent Name

[Ervan]

Hi Guys , I am struggling to separate this worksheet in my work place . i have thousand cell with this type and i would like to separate them

Name (a cell )
Badrol Zaman bin Abdul Samad
Nurhayati Sholeha binti Abdur Rahem

real name ( b cell )should be :

Badrol Zaman
Nurhayati Sholeha

Parent name ( c cell ) should be :

Abdul Samad
Abdur Rahem


Any help will much appreciated

 

[Michael M]

Hi and welcome to the Board
try
Badrol Zaman bin Abdul Samad Abdul Samad Badrol Zaman
Nurhayati Sholeha binti Abdur Rahem Abdur Rahem Nurhayati Sholeha

IN B1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",60)),120))

In C1
=LEFT(A1,SEARCH(" bin*",A1,1))

copy down as required

 

[Rick Rothstein]

Try this...

1) Select all of Column A

2) Press CTRL+H to bring up the Replace dialog box

3) Put <space>bin<space> in the "Find what" field (use a space character in place of each <space>)

4) Put a # sign in the "Replace with" field.

5) Click the "Options>>" button to reveal all possible options and make sure the "Match entire cell contents" check box is not checked.

6) Click the "Replace All" button.

7) With the Replace dialog box still up, repeat steps 3 thru 6 except use <space>binti<space> at step #3

8) Close the dialog box and bring up the "Text to Columns" dialog box (method to do this varies with the Excel version, so you are on your own to find it).

9) At Step #1, select the Delimited option button.... click the Next button.

10) At Step #2, make sure the Space checkbox is not checked and put a # sign in the empty field next to the Other checkbox (this will put a check mark in that checkbox which is what you want to happen)... click the Next button.

11) At Step #3, put the destination cell for the first output cell (if your data starts in A1, then use B1... if your data starts in A2, then use B2... and so on)... click the Finish button.

 

[Michael M]

Hmm, seems my HTML didn't work
2nd go.....

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Badrol Zaman bin Abdul Samad </td><td style=";">Abdul Samad</td><td style=";">Badrol Zaman </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Nurhayati Sholeha binti Abdur Rahem </td><td style=";">Abdur Rahem</td><td style=";">Nurhayati Sholeha </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">A1</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),200</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,SEARCH(<font color="Red">" bin*",A1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">A2</font>)," ",REPT(<font color="Purple">" ",100</font>)</font>),200</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,SEARCH(<font color="Red">" bin*",A2,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Ervan]

Thank you guys , it work perfectly. Great


Regards


Ervan