Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-694618%22%20slang%3D%22en-US%22%3EPulling%20numerical%20field%20from%20a%20string...%20in%20excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EDear%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20an%20excel%20sheet%20of%26nbsp%3B%20text%20string%20like....%3C%2FSPAN%3E%3CBR%20%2F%3E%3CEM%3E%3CSPAN%3ECell%20A1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20....%20employee_code%20%3D%2025147%20and%20trans_code%3D16....%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FEM%3E%3CEM%3E%3CSPAN%3ECell%20A2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20....%26nbsp%3B%3C%2FSPAN%3E%3C%2FEM%3E%3CEM%3E%3CSPAN%3Eemployee_code%20%3D%2025112%20and%20trans_code%3D16....%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FEM%3E%3CEM%3E%3CSPAN%3ECell%20A3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20....%26nbsp%3B%3C%2FSPAN%3E%3C%2FEM%3E%3CEM%3E%3CSPAN%3Eemployee_code%20%3D%2018512%20and%20trans_code%3D16....%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20so%20on%20...%3CBR%20%2F%3Ei%20want%20to%20pull%20employee%20code%20from%20above%20string%26nbsp%3B%20in%20corresponding%20an%20excel%20cell%26nbsp%3B%20like%20B1%3A%2025147%20B2%3A25112%20and%20B3%3A18512%3CBR%20%2F%3ENote%20-%20before%20employee%20code%20there%20is%20a%20string%20and%20after%20%3D16%20there%20is%20string.%3CBR%20%2F%3E%3CBR%20%2F%3ERequest%20for%20any%20formula%20or%20assistance%20for%20solution...%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3Ealigahk06%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-694618%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694647%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20numerical%20field%20from%20a%20string...%20in%20excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361061%22%20target%3D%22_blank%22%3E%40aligahk06%3C%2FA%3EThe%20simple%20action%20is%20to%20use%20the%20Data%20text%20to%20column%20tool%20where%20the%20separator%20is%20%3D%3C%2FP%3E%3CP%3Eand%20another%20separator%20is%20the%20space%20character%2C%20then%20removing%20other%20unneeded%20columns.%3C%2FP%3E%3CP%3EYou%20can%20find%20the%20%22Text%20to%20column%22%20tool%20here%20in%20the%20Data%20tab%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118222iCD629FED5A99FD01%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22texttocolumn.jpg%22%20title%3D%22texttocolumn.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20Excel%202013%20or%20later%20version%2C%20there%20is%20another%20solution%20can%20be%20achieved%20using%20Flash%20Fill.%20Type%20manually%20the%20first%20code%20extracted%20as%20you%20need%2C%20then%20select%20the%20whole%20column%20and%20click%20Flash%20Fill%2C%20you%20can%20find%20Flash%20Fill%20under%20the%20fill%20menu%20in%20the%20Home%20tab%20as%20shown%20in%20the%20following%20image%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118223i844ADD59BEF136F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22flashfill.jpg%22%20title%3D%22flashfill.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOtherwise%2C%20using%20formulas%20will%20add%20an%20advantage%2C%20which%20is%20automatic%20update%20if%20the%20original%20data%20change.%20If%20the%20employee%20code%20is%20stick%20to%205%20characters%20you%20can%20use%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3DMID(A1%2CSEARCH(%22employee_code%20%3D%20%22%2CA1)%2B16%2C5)*1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20employee%20code%20varies%20from%205%20characters%2C%20we%20may%20need%20to%20add%20another%20part%20to%20the%20formula%20above%20to%20solve%20this.%20Please%20let%20me%20know%20if%20you%20need%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
aligahk06
Occasional Visitor

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.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies