Formula to pull constant number string from variable text strings

I have a Notes column that often includes between one and five 9-digit number strings somewhere within the text. In Excel, I used the formula below to pull the numbers from the text into one or more subsequent columns.

=LET(arr,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N84,"'"," "),";"," "),","," ")," "),FILTER(arr,ISNUMBER(-arr)*(LEN(arr)=9)))

Results look like this

Since the LET function does not exist in Smartsheet, how can I reproduce this result?

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

Yes, it was a challenge. @ljpettr1 @Lucas Rayala

The Activity Log shows it took more than two hours.

The solution below consists of three steps.😅

1) Delete non-numeric characters with SUBSTITUTE

With a gigantic SUBSTITUTE formula, I removed all the non-numeric characters.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER([N84]@row), "a", ""), "b", ""), "c", ""), "d", ""), "e", ""), "f", ""), "g", ""), "h", ""), "i", ""), "j", ""), "k", ""), "l", ""), "m", ""), "n", ""), "o", ""), "p", ""), "q", ""), "r", ""), "u", ""), "s", ""), "t", ""), "u", ""), "v", ""), "w", ""), "x", ""), "y", ""), "z", ""), "/", ""), ";", ""), "&", ""), ",", ""), "?", ""), "-", "")

Since I use this type of substitution often, I have a tool like this that helps me do that.

2) Find the location of " " or spaces and extract all the numbers.

For details, please look at the published demo sheet. (You can see the formula by clicking the fx in columns.) I developed this technique to extract Single Contact from Multiple Contacts. (Since it extracts up to 20 items.)

https://app.smartsheet.com/b/publish?EQBCT=1185912ff4174710a6d986cf6add3a7d

[s1] =IF([m#]@row > 1, FIND(CHAR(10), [Multiple Dropdown]@row, [s0]@row + 1))

[s2] =IF([m#]@row > 2, FIND(CHAR(10), [Multiple Dropdown]@row, [s1]@row + 1))

[n1] =IF([s1]@row > [s0]@row, MID([Multiple Dropdown]@row, [s0]@row + 1, [s1]@row - [s0]@row), IF([s1]@row < [s0]@row, MID([Multiple Dropdown]@row, [s0]@row + 1, LEN([Multiple Dropdown]@row) - [s0]@row + 1)))

[n2] =IF([s2]@row > [s1]@row, MID([Multiple Dropdown]@row, [s1]@row + 1, [s2]@row - [s1]@row), IF([s2]@row < [s1]@row, MID([Multiple Dropdown]@row, [s1]@row + 1, LEN([Multiple Dropdown]@row) - [s1]@row + 1)))

This s1-20 formula uses [m#], the number of items in a dropdown list. So, I converted the step 1 result to a dropdown list with this formula.

=SUBSTITUTE([Delete non-numeric characters with SUBSTITUTE]@row, " ", CHAR(10))

3) Get the "9 digits numbers starting with 20" with JOIN and COLLECT

=JOIN(COLLECT([n1]@row:[n20]@row, [n1]@row:[n20]@row, LEN(@cell) = 9, [n1]@row:[n20]@row, FIND("20", @cell) > 0), CHAR(10))

Compared to your Excel's compact and elegant formulas, this solution requires a lot of repetition and brute force. There is still room for improvement in Smartsheet's functions.

If you want a copy of the sheet, please get in touch with me at info@cloudsmart.jp.

jmyzk_cloudsmart_jp

Hi @ljpettr1

Referring to @Paul Newcome 's idea of using "some way off the wall symbol that I know will never be found in the string", I have updated the sheet I showed you earlier.

This eliminates the m#, s0,s1, s,20 helper columns.

The expressions are now shorter, which may improve performance, if only slightly. However, the previous formula works fine, so it depends on whether you want to take the trouble to update it.

https://app.smartsheet.com/b/publish?EQBCT=3c74558f46e54c47abef2400deecc8ac

[List+] =SUBSTITUTE([Multiple Dropdown]@row, CHAR(10), DLM#) + DLM#

[n1] =LEFT([List+]@row, FIND(DLM#, [List+]@row) - 1)

[n2] =IFERROR(IF(ISTEXT([n1]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, "")) - 1)), "")

[n3] =IFERROR(IF(ISTEXT([n2]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, "")) - 1)), "")

[n4] =IFERROR(IF(ISTEXT([n3]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")) - 1)), "")

DLM# is a rare character chosen in the Sheet Summary field. (Example: "¬")

Hi @Paul Newcome

I have devised a way to Extract the list from the Multiple Dropdown List using your idea of replacing delimiter such as CHAR(10), ",", etc. with generally unused characters.

https://app.smartsheet.com/b/publish?EQBCT=5a6239214e164ecea31e1a1431aad35e

List+

The [List+] column's formula is the following.

=SUBSTITUTE([Multiple Dropdown]@row, CHAR(10), DLM#) + DLM#

The + DLM# at the end facilitates retrieval of the last Item.

Items are retrieved in a structure, LEFT(TEXT, FIND(DLM#, TEXT)-1), but if the DLM# is to the right of the last Item, the last Item can also be retrieved using the same form of expression.

Last Item¬.

=LEFT("Last Item¬", FIND("¬", "Last Item¬") - 1)

output: Last Item

For example, if the following items are selected in the Multiple Dropdown List, then the formula will produce "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬".

Bangkok
Buenos Aires
Bangkok Buenos Aires
Los Angeles
New York City
Paris

To get the first Bangkok in this "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬" example

Text = "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬", you can use a formula of the following form

LEFT(TEXT, FIND(DLM#, TEXT)-1)

=LEFT("Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬", FIND("¬", "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris ¬") - 1)

Stepwise Substitution

After the second stage, items can be sequentially obtained by applying the above formula LEFT(TEXT, FIND(DLM#, TEXT)-1) to the TEXT, excluding the Item + DLM# obtained in the previous stage.

Use the following formula to remove items up to the previous stage + DLM#.

n th Item

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n-1]@row, DLM#) + DLM#, "")

For example, "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬" is replaced by "Buenos Aires¬London¬ Los Angeles¬New York City¬Paris¬" in the second formula, and LEFT(TEXT, FIND(DLM#, TEXT)-1) can be used to extract Buenos Aires.

2nd.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, "")

output: "Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬"

After the third, the structure of the expression is simplified because we only increment [n1] on the right side of JOIN([n1]@row:[n1]@row).

3rd.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, "")

output: "London¬Los Angeles¬New York City¬Paris¬"

4th.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")

output: "Los Angeles¬New York City¬Paris¬"

Whole formula (4th item example)

[L4] =IFERROR(IF(ISTEXT([n3]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")), FIND(DLM#, SUBSTITUTE([List+]@row, "") JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")) - 1))), "")

Supplemental Explanation

IFERROR(, ""))

We will remove [Item + DLM#] obtained in the previous step, and if it becomes blank and FIND results in error, it is the end, so return blank ""

IF(ISTEXT([nx]@row),")

When the blank space enters the previous stage in the above IFEEOR, JOIN([n1]@row:[nx]@row, DLM#) + DLM# adds DLM# for the blank space as shown in the example below. , so add an IF expression to limit it only if the previous Item is not blank and is text.

"Los Angeles¬New York City¬Paris¬¬¬¬¬"

All comments

Lucas Rayala

@ljpettr1 — Smartsheet functions aren’t set up to handle this challenge yet. Any chance these numbers have a consistent length? And any chance they’ll have a consistent or determinable first few characters? You can fish them out if they’re consistent but otherwise it’s a challenge.

ljpettr1

@Lucas Rayala - the numbers are always 9 digits long and always start with "20".

jmyzk_cloudsmart_jp

Yes, it was a challenge. @ljpettr1 @Lucas Rayala

The Activity Log shows it took more than two hours.

The solution below consists of three steps.😅

1) Delete non-numeric characters with SUBSTITUTE

With a gigantic SUBSTITUTE formula, I removed all the non-numeric characters.