remove . and @ then split name into 2 col

[Trevor3007]

Hi,

Within a range ( a1..a500)I need to place the first name & surname in to retrospective columns . Below is an example

Billy.twoshoes1234@sole.uk

I need to remove the . , number & @sole.uk .

[TABLE="width: 500"]
<tbody>[TR]
[TD]First name[/TD]
[TD]surname [/TD]
[/TR]
[TR]
[TD]billy [/TD]
[TD]twoshoes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then have the 1st name in one col & the surname in another ( as shown above).



Although I can get the first name via ‘ =LEFT(X2, FIND(".", X2&"?")-1) ‘, I cannot get the surname?

Can anyone sort this issue for me?

Many thanks in advance…..

KR

 

[JoeMo]

Is the surname always followed by 4 digits and @?

 

[Marcílio_Lobão]

Trevor3007, Good afternoon.

Try to use:

First name --> =LEFT(A1, SEARCH(".", A1)-1)

Surname --> =MID(A1, SEARCH(".", A1)+1, SEARCH({1\2\3\4\5\6\7\8\9\0},A1)-(SEARCH(".", A1)+1))

Is that what you want?
I hope it helps.

Marcílio Lobão

 

[jim may]

First, in your workbook's VBA in a Standard module paste in the following UDF (User Defined Function):

Function Getnum(inpt As String)
     Dim i As Integer
     For i = 1 To Len(inpt)
         If IsNumeric(Mid(inpt, i, 1)) Then
             Getnum = i
             Getnum = CLng(Getnum)
         Exit Function
         End If
     Next i
     Getnum = CLng(Getnum)
End Function

Excel 2010

	A	B	C	D	E
Billy.twoshoes1234@sole.uk	<< The UDF provides you with the Character # of the FIRST Numeric.
>>>> HERE YOU GO !!! >>>>>	Billy	Twoshoes

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]C3[/TH]
[TD="align: left"]=Getnum(B3)[/TD]
[/TR]
[TR]
[TH="width: 10"]C5[/TH]
[TD="align: left"]=LEFT($B3,FIND(".",B3,1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10"]D5[/TH]
[TD="align: left"]=Proper(MID($B3,FIND(".",$B3)+1,Getnum($B3)-FIND(".",$B3)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

Excel 2010
	A	B	C
1	Billy.twoshoes1234@sole.uk	Billy	twoshoes
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(".",A1&".")-1)
C1	=REPLACE(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-1),1,LEN(B1)+1,"")

 

[Trevor3007]

Thank you all for your help and because of this help, my problem is resolved. thank you all again.

KR

Trevor3007