How to extract a number from a cell that has text+numbers using vlookup function
Hi all,
I'm pulling Line numbers from another production schedule in the format of:
Line01, Line02, etc.
and I'm trying to perform a VLOOKUP FUNCTION using the WO number to pull the value from another sheet. As you can see below I'm able to do so easily in the example.
The problem I'm having is that we recently introduce new Line#s where it includes Line 03.1 and Line 03.2 in a couple of the lines  meaning I can't use the "RIGHT" function as the length of the string varies.
This is the formula I am trying to use to extract the numbers from the vlookup.
=LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4) + " " [this part of the formula works]
+ VALUE(RIGHT(VLOOKUP(WO@row, {Table1}, 2, false), FIND(" ", VLOOKUP(WO@row, Table1}, 2, false) + 1))) [not pulling any values]
I tried to reference the formula used in this previous discussion: https://community.smartsheet.com/discussion/75839/howwouldyouextractanumberfromacellthatcontainstextandnumbers
Any help or guidance would be much appreciated. I feel like I'm close but the added layer of having to VLOOKUP the value messes up the formula.
Thanks.
Taylor
Best Answer

Hi, @tdotarcy, try the formula below.
= LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4) + " " + VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))
Assuming that the Line#'s will always start with "Line", the formula will produce: "Line 6" from "Line06", "Line 3.1" from "Line 03.1", "Line 3.1" from "Line03.1".
Explanation
Use LEFT() to return "Line". LEFT( ,4) ...or...
LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4)
The VALUE() function will result in an error if the text contains nonnumeric characters like a space " ". So...
Use SUBSTITUTE() to remove the space if it is present. SUBSTITUTE( , " ", "") ...written out...
SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", "")
The number of characters to the right of the word "Line" is the LEN() of the string "4". LEN( )4 ...or...
LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4
Use RIGHT() to return the numbers. RIGHT( , LEN( )4 )
Take the VALUE(). VALUE(
RIGHT( , LEN()4 )
) ...the full expression...VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))
LEFT() + " " + VALUE() ...the complete formula...
= LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4) + " " + VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))
Answers

Hi, @tdotarcy, try the formula below.
= LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4) + " " + VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))
Assuming that the Line#'s will always start with "Line", the formula will produce: "Line 6" from "Line06", "Line 3.1" from "Line 03.1", "Line 3.1" from "Line03.1".
Explanation
Use LEFT() to return "Line". LEFT( ,4) ...or...
LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4)
The VALUE() function will result in an error if the text contains nonnumeric characters like a space " ". So...
Use SUBSTITUTE() to remove the space if it is present. SUBSTITUTE( , " ", "") ...written out...
SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", "")
The number of characters to the right of the word "Line" is the LEN() of the string "4". LEN( )4 ...or...
LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4
Use RIGHT() to return the numbers. RIGHT( , LEN( )4 )
Take the VALUE(). VALUE(
RIGHT( , LEN()4 )
) ...the full expression...VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))
LEFT() + " " + VALUE() ...the complete formula...
= LEFT(VLOOKUP(WO@row, {Table1}, 2, false), 4) + " " + VALUE(RIGHT(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table1}, 2, false), " ", ""))4 ))

Hey Toufong, it didn't give me the exact result that I desired but I was able to use the LEN(SUBSTITUTE) function to get the desired result. Thanks so much.
Here was my final formula if anyone else wants to find a similar solution.
=LEFT(VLOOKUP(WO@row, {Table 1}, 2, false), 4) + " " + RIGHT(VLOOKUP(WO@row, {Table 1}, 2, false), LEN(SUBSTITUTE(VLOOKUP(WO@row, {Table 1}, 2, false), " ", 0))  4)
This gives me the result of
Line06 > Line 06 &
Line03.2 > Line 03.2
Line11 > Line 11
Cheers,
Taylor
Help Article Resources
Categories
Check out the Formula Handbook template!