How to extract a number from a cell that has text+numbers using vlookup function

tdotarcy
tdotarcy ✭✭
in Formulas and Functions

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.

image.png

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

· Share on FacebookShare on Twitter

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    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 ))

    · Share on FacebookShare on Twitter

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    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 ))

    · Share on FacebookShare on Twitter
  • tdotarcy
    tdotarcy ✭✭
    https://community.smartsheet.com/discussion/comment/357913#Comment_357913

    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!