Pulling numerical field from a string... in excel sheet

Copper Contributor

Dear All,

I have an excel sheet of  text string like....
Cell A1        .... employee_code = 25147 and trans_code=16....
Cell A2        .... employee_code = 25112 and trans_code=16....
Cell A3        .... employee_code = 18512 and trans_code=16....  and so on ...
i want to pull employee code from above string  in corresponding an excel cell  like B1: 25147 B2:25112 and B3:18512
Note - before employee code there is a string and after =16 there is string.

Request for any formula or assistance for solution...

Regards,
aligahk06

1 Reply

@aligahk06The simple action is to use the Data text to column tool where the separator is =

and another separator is the space character, then removing other unneeded columns.

You can find the "Text to column" tool here in the Data tab:

texttocolumn.jpg

 

If you use Excel 2013 or later version, there is another solution can be achieved using Flash Fill. Type manually the first code extracted as you need, then select the whole column and click Flash Fill, you can find Flash Fill under the fill menu in the Home tab as shown in the following image:

flashfill.jpg

Otherwise, using formulas will add an advantage, which is automatic update if the original data change. If the employee code is stick to 5 characters you can use the following formula:

=MID(A1,SEARCH("employee_code = ",A1)+16,5)*1

 

If the employee code varies from 5 characters, we may need to add another part to the formula above to solve this. Please let me know if you need it.