Text field may contain a 5 digit code, and if so need to extract it to its own column

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3157910%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EText%20field%20may%20contain%20a%205%20digit%20code%2C%20and%20if%20so%20need%20to%20extract%20it%20to%20its%20own%20column%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3157910%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3ESo%20my%20title%20data%20sometimes%20contains%20a%20code%20that%20tells%20me%20important%20information%20about%20the%20item%2C%20my%20ultimate%20goal%20is%20to%20use%20this%20code%20with%20a%20vlookup%20function%20to%20autofill%20other%20fields.%20My%20problem%20is%20that%20it%20is%20intermingled%20with%20other%20information%20ex%3A%26lt%3B%5C%2FP%26gt%3B%3CTABLE%20width%3D%22%5C%26quot%3B568%5C%26quot%3B%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22%5C%26quot%3B568%5C%26quot%3B%22%3E13112%20Way%20to%20Celebrate%20Men's%20%26amp%3B%20Big%20Men's%20Hoppy%20Halloween%20Graphic%20Tee%20Small%26lt%3B%5C%2FTD%26gt%3B%26lt%3B%5C%2FTR%26gt%3B%26lt%3B%5C%2FTBODY%26gt%3B%26lt%3B%5C%2FTABLE%26gt%3B%3CP%3EI%20think%20what%20I%20need%20is%20a%20function%20that%20will%20see%20that%20there%20is%20a%205%20digit%20numeric%20value%20contained%20within%20the%20text%20and%20move%20only%20that%20number%20to%20a%20new%20field%20that%20I%20could%20then%20use%20for%20the%20vlookup%20function.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3157910%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

So my title data sometimes contains a code that tells me important information about the item, my ultimate goal is to use this code with a vlookup function to autofill other fields. My problem is that it is intermingled with other information ex:

13112 Way to Celebrate Men's & Big Men's Hoppy Halloween Graphic Tee Small

I think what I need is a function that will see that there is a 5 digit numeric value contained within the text and move only that number to a new field that I could then use for the vlookup function.

2 Replies

@Modnar1 

 

=LET(
a,--LEFT(A1,5),
IF(ISNUMBER(a),a,"")
)

 

Hello @Modnar1 

 

If they data are in the same format (the numbers you need to extract) is 5 digits and always at the beginning of the text, then you would use this formula

=LEFT(A1,5)   --> this way the result will be 13112 as a text 

=NUMBERVALUE(LEFT(A1,5))  --> the result will be converted into numbers,