Text Forumla error

krzysiek

New Member
Hi All,

Hope any of You can help me with below formula?

I am having a problem with creating the formula that returns name and surname when it is in table, and on few occasions there might be an e-mail address (Name.Surname@companyname.com) in this sitation the formula should extract name and surname from e-mail address.

=IF(ISERROR(FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),1)),VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),PROPER(LEFT(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1)&" "&PROPER(MID(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))+1,FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1))))

it works to some extent,
when formula finds @ character both IF conditions are correct, but when it does not find @ character the second condition PROPER(LEFT(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1)&" "&PROPER(MID(VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0),FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))+1,FIND("@",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-FIND(".",VLOOKUP(B7,'G''Fees Non-back up'!B:J,8,0))-1))) is rerutning error.

Possibly any of You have an idea how to correct this?

Regards,

Krzysiek

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Can you provide a sample of the data?

Replies
0
Views
158
Replies
4
Views
324
Replies
3
Views
330
Replies
4
Views
230
Replies
12
Views
458

1,203,047
Messages
6,053,197
Members
444,645
Latest member
mee siam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back