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 .

First name	surname
billy	twoshoes

<tbody>
</tbody>

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
2
3		Billy.twoshoes1234@sole.uk	15		<< The UDF provides you with the Character # of the FIRST Numeric.
4
5		>>>> HERE YOU GO !!! >>>>>	Billy	Twoshoes

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C3 =Getnum(B3) C5 =LEFT($B3,FIND(".",B3,1)-1) D5 =Proper(MID($B3,FIND(".",$B3)+1,Getnum($B3)-FIND(".",$B3)-1)) <tbody> </tbody>

<tbody>
</tbody>

 

[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