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

tdotarcy

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

Toufong Vang

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 ))

Toufong Vang

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 ))

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