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
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!