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/how-would-you-extract-a-number-from-a-cell-that-contains-text-and-numbers
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 non-numeric 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 non-numeric 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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!