SOLVED

Return a Number Value That Follows Text in a Linked Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2513865%22%20slang%3D%22en-US%22%3EReturn%20a%20Number%20Value%20That%20Follows%20Text%20in%20a%20Linked%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513865%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20%26nbsp%3BHow%20can%20I%20return%20a%20number%20value%20that%20follows%20a%20text%20string%20in%20a%20linked%20cell.%20%26nbsp%3BSo%2C%20if%20a%20cell%20contains%20%22CAA10%2FBLE15%2FUNTITLED%205%22%20and%20I%20want%20the%20destination%20cell%20to%20always%20display%20the%202%20digit%20number%20that%20immediately%20follows%20%22BLE%22%20(in%20this%20case%2015)%2C%20how%20could%20I%20do%20that%3F%20%26nbsp%3BHere%20is%20an%20example%20of%20the%20possible%20source%20cell%20contents.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-07-03%20at%208.36.33%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293331i4703973E0AD17CBE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-07-03%20at%208.36.33%20PM.png%22%20alt%3D%22Screen%20Shot%202021-07-03%20at%208.36.33%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3EJoseph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2513865%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513914%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20Number%20Value%20That%20Follows%20Text%20in%20a%20Linked%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1094997%22%20target%3D%22_blank%22%3E%40JosephA1915%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(VALUE(MID(A1%2CFIND(%22BLE%22%2CA1)%2B3%2C2))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20the%20text%20string%20is%20in%20A1.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2514420%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20Number%20Value%20That%20Follows%20Text%20in%20a%20Linked%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2514420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20do%20much.%20%26nbsp%3BThis%20seems%20to%20work!%20%26nbsp%3BCan%20I%20ask%20what%20the%20%22%2B3%22%20in%20the%20MID%20formula%20is%20for%20rather%20than%20just%20a%20%223%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello.  How can I return a number value that follows a text string in a linked cell.  So, if a cell contains "CAA10/BLE15/UNTITLED 5" and I want the destination cell to always display the 2 digit number that immediately follows "BLE" (in this case 15), how could I do that?  Here is an example of the possible source cell contents.

 

Screen Shot 2021-07-03 at 8.36.33 PM.png

 

Thank you, 

Joseph

5 Replies
best response confirmed by JosephA1915 (Occasional Contributor)
Solution

@JosephA1915 Try this:

=IFERROR(VALUE(MID(A1,FIND("BLE",A1)+3,2)),"")

where the text string is in A1.  

@Riny_van_Eekelen 

 

Thank you do much.  This seems to work!  Can I ask what the "+3" in the MID formula is for rather than just a "3"?

Thank you "SO" much;)

@JosephA1915 The FIND function finds the first character of the string "BLE". Then you need to add three characters (B,L and E) to arrive at the first position that could be a number, 

Awesome! Thank you:)