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?
Best Answers
-
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.)
[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.
-
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: "¬")
- ¬
- §
- ¶
- Ñ
- ~
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.
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)
OR
=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¬¬¬¬¬"
Answers
-
@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.
-
@Lucas Rayala - the numbers are always 9 digits long and always start with "20".
-
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.)
[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 this is incredible! I can't wait to start getting this put together for my sheet. I appreciate the time you took to work this one out. There are two other numbers that I need to pull and I see that I can adjust this solution to find those too.
I cannot thank you enough - you have saved me literal days, maybe even weeks of work!
-
This could be greatly simplified if you could ensure there is always an "SO " before each of the numbers. Looking at your screenshot, I only see one instance where this is not the case. Is it possible you could get your team to consistently do that with the data entry? Or possibly ensure that if they are stringing multiple SO #s together they consistently use the "&" between (as in that instance) and that is the only time they use the "&"?
-
-
Was going to mention this. Also, depending on the level of data quality, even without the SO you can make a lot happen with just the " 20", i.e. a space followed by the number 20. Not to cast shade on @jmyzk_cloudsmart_jp extremely robust solution!
@Paul Newcome -- is there a Best Practice / Guide somewhere around the issue of pulling multiple entries from a bulk of text? This issue pops up often, where people need to parse data into multiple columns (same principal applies to parsing dropdown values, essentially).
-
@Lucas Rayala I thought about the "space 20" as well, but there is an entry that indicates "11 ..... kits". If that number of kits happened to be 20, then the logic would grab pieces of string that we don't want. That's why I was looking at the "SO space" as the indicator.
I always suggest a consistent but unique indicator. Consistent in that it comes immediately before each instance that needs parsed out, and unique in that the only time that indicator appears in the string is when there is something to be parsed out.
From there I use the MID/SUBSTITUTE method to grab 1st, 2nd, 3rd, etc. entry from the string the same as if we had a list where every piece gets parsed out such as from a multi-select dropdown. The logic and formulas are still the same. In this particular instance though, we know the character count is a set number, so that simplifies the MID/SUBSTITUTE method even further.
=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "SO ", "~", 1)) + 1, 9)
The "~" shouldn't ever be found anywhere in the text. You can use whatever character you want. I have even substituted out with a UNICHAR function of some way off the wall symbol that I know will never be found in the string.
The first 1 (the one inside of the SUBSTITUTE function) tells you to pull the 1st set. Change that to a 2 for the 2nd set, so on and so forth.
With the variable number of sets to pull, I would suggest getting it up and running and then wrapping it in an IFERROR so that cells are left blank when there aren't that many sets to pull.
-
I am working on a project to pull these numbers from almost 10 years of historical data. That means, unfortunately, the data is what it is. I have implemented updated processes and added columns for these SO #s that used to only be captured in the Notes section but that still leaves me with the problem of populating the data after the fact.
Since the numbers need to be manually audited prior to moving them into the appropriate columns, I'm pretty sure the solution using " 20" as the unique indicator would be effective. In fact, as these SO's count up through the years, a better solution may be to use the first 3 or 4 digits and update them as I work through the years. The first orders start in 2015 with "2000" and then count up until we hit "2014" in 2023. Using the first three digits could capture data from 2015 through 2020 with "200" and then 2020-2023 could be captured with "201".
Would you mind showing me an example of the MID/SUBSTITUTE method using these numbers as the unique indicators? I'm not clear on how to use it to pull multiple entries.
-
@ljpettr1 The MID function allows us to pull characters out of a text string based on a starting point and number of characters. Usually when trying to locate a variable starting point based on a consistent indicator, we use the FIND function which will output a number based on where a specific character was found within a string.
For example, if we have the string "ABCDEFGHIJKLMNOP" and we use the FIND function to look for the letter "C", it will output a 3.
=FIND("C", "ABCDEFGHIJKLMNOP")
So if we wanted to pull 5 characters starting with the letter "C" but the letter "C" could be anywhere within the string, we would use
=MID("ABCDEFGHIJKLMNOP", FIND("C", "ABCDEFGHIJKLMNOP"), 5)
The challenge comes when there are multiple instances of the letter "C" that need to be pulled. In that case we still use the FIND function, but we leverage the SUBSTITUTE function because it allows us to specify which instance fo the letter "C" we want to substitute.
What we can do is use the SUBSTITUTE function to replace the first "C" with something else that won't be found in the text string such as "~". Now we can FIND the "~" and know the starting point of our first piece to pull.
Then we can slightly tweak that same formula and change the 1 in the SUBSTITUTE function telling it to replace the first "C" to a 2 to tell it to replace the second "C". Now when we FIND the "~", it will tell us the location of our second "C".
We also know that the FIND function will output the location of the first character if we are telling it to find a string.
=FIND("CDE", "ABCDEFGHIJKLMNOP")
will output 3 because that's where the string we told it to find starts, but because we included extra characters we know it will skip over instances of "C" being by itself or followed by other characters.
We can roll all of these pieces together to SUBSTITUTE the nth "200" with a "~", FIND the "~", and then use that as the starting point of the MID function.
=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 1), 9)
Then the second 9 character string that starts with "200" would be
=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 2), 9)
And the third...
=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 3), 9)
So on and so forth. You would pre-populate these formulas into a series of helper columns. To keep it from throwing an error in the 3rd column if there are only two SO #s, we would wrap each on in an IFERROR.
=IFERROR(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 1), 9), "")
.
Now for the "tricky" part. You say that some could start with "200" and others could start with "201". I am sure that we could write out some messy formula that can roll both into a single set of columns, but honestly the easiest way to build and manage this would be to have a set of columns that pull the "200" numbers and a second set of columns that pull the "201" numbers. There are a couple of options for getting rid of the empty cells in between that we can explore, but that will require a few more questions. I would suggest getting this part up and running first before trying to tackle that piece.
-
@jmyzk_cloudsmart_jp I saw your comment about using your method to extract single contacts out of a multi-contact cell. You can do this without the multiple columns per contact by using the SUBSTITUTE method as well. The only difference being that the MID function wouldn't have a hard coded number of characters to pull.
To work with this, you start the string off and end it with your delimiter. Then you can use the FIND/SUBSTITUTE combo to automatically locate the next delimiter and subtract the starting delimiter from it. The below is actually for a multi-select dropdown instead of a multi-select contact, but it is still the same principle.
=IFERROR(MID("~" + SUBSTITUTE([Column Name]@row, CHAR(10), "~") + "~", (FIND("!", SUBSTITUTE("~" + SUBSTITUTE([Column Name]@row, CHAR(10), "~") + "~", "~", "!", 1)) + 1), FIND("!", SUBSTITUTE("~" + SUBSTITUTE([Column Name]@row, CHAR(10), "~") + "~", "~", "!", 2)) - (FIND("!", SUBSTITUTE("~" + SUBSTITUTE([Column Name]@row, CHAR(10), "~") + "~", "~", "!", 1)) + 1)), "")
We basically have
=MID(string, 1, 2 - 1)
Where "1" is the starting position of the first string and "2" is the starting position of the second string. To pull the second string, we would use
=MID(string, 2, 3 - 2)
So on and so forth.
-
@Paul Newcome -- this is an awesome solution. I never thought of using the substitute like this, so I've always had a complicated (and glitchy) "FIND" function columns 2, 3, etc, that looked back to the previous columns and used those inputs to FIND the instance after that. Total mess, this is so much cleaner. This needs to be a best practice article, if it's not already. Bookmarking!
-
@Lucas Rayala As much as I would love to take credit for it, I can't. I saw someone else here in the Community suggest the SUBSTITUTE function, and I have been using and tweaking it ever since. I just can't remember who had posted it when I first saw it to give them the kudos.
My favorite way to use it is to use a text/number column that I manually enter the numbers 1 through whatever in and then use that as a cell reference in place of the numbers to substitute.
Number@row
Number@row + 1
Number@row
Then I can make it a column formula and really get some parsing done.
-
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: "¬")
- ¬
- §
- ¶
- Ñ
- ~
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.
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)
OR
=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¬¬¬¬¬"
-
Lots of great work! It's interesting that the substitute command reorders the list. I have a feeling this gets to why a different method I've previously used occasionally gacked on my.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
