find first name,last name,id,domain name as given example

[Ramesh Deo]

Hello SM Sir & All,
good evening!
Please solve this query simply using formula!!!

 

[Xen]

Easy enough, just find special characters, count distances between them, then cut string apart using left/right/mid

BTW it should be easy as pie in excel 2013; just type how you want to break down your string and press ctrl+e

 

[Somendra Misra]

Hi Ramesh,

See the attached file. The formulas will work if the pattern mentioned in the files remain same.

Regards,

 

[Ramesh Deo]

Easy enough, just find special characters, count distances between them, then cut string apart using left/rig
BTW it should be easy as pie in excel 2013; just type how you want to break down your string and press ctrl+e

THanks XEN!!!

 

[Ramesh Deo]

Hi Ramesh,

See the attached file. The formulas will work if the pattern mentioned in the files remain same.

Regards,

Thakns SM Sir!!! please tell me abot using FIND({0,1,2,3,4,5,6,7,8,9},A4, ,what result comes by using this.

 

[Xen]

Sorry, I realized my formula has problems looking for first number! Unless all your ID start with 01, it will return incorrect results!
Please use SM's solution, it is prefect. It looks for all the numbers and then returns first occurrence thanks to MIN and array formula.

What simple FIND ("x") does is returning first occurrence of "x" (say, 13) in string or #VALUE if it is not there, by putting multiple character to look for in brackets you tell FIND to look for all the characters, so function looks for "x", "y" and "z", and returns multiple results like: FIND({x,y,z}) = {13, #VALUE, 20} if we have X and Z in text, but not Y. Then, to make use of the result, you need array formula, or else you will only work with first result (that was the mistake I made).

 

[Somendra Misra]

This to find if any numbers are present as your user id are numbers.

Regards,

 

[Ramesh Deo]

k,i thought smthing else.theanks SM sir