May 03 2024 11:04 AM
I have a cell that contains this: 00001/ / /29100/ I would like 2 separate numeric cells, one cell that would contain 00001 and the second cell that contains 29100 omitting the slash marks completely.
Can this be done? I don't have much experience with Excel.
Thank you for your help.
May 03 2024 11:55 AM
Yes it's possible. For general learning in the future, let me refer you to this page of text functions.
If ALL of your situations are like this one, 00001/ / /29100/, consisting of two string of five digits with slash marks in those same positions, a couple very basic functions to yield just the two strings of five digits, one each in a separate cell, would be (with the starting assumption that the original is in cell A1
=LEFT(A1, 5)
and
=MID(A1,LEN(A1)-5,5) or =LEFT(RIGHT(a1,6),5)
May 03 2024 12:03 PM - edited May 03 2024 12:14 PM
SolutionYou could also employ =CHOOSECOLS(TEXTSPLIT(A1,"/"),1,4) if you have a new version of Excel or subscription to Microsoft 365. This formula has the advantage of working even if the strings of digits vary in length. (If the number of slash marks change, you will need to adjust up or down the final digit to reflect that.)
May 03 2024 02:27 PM
More variants.
=TEXTSPLIT(TRIM(SUBSTITUTE(A1,"/",""))," ")
=SUBSTITUTE(TEXTSPLIT(A1,"/ / /"),"/","")
May 04 2024 03:10 PM
The formula
= TEXTSPLIT(string, {"/"," "}, , TRUE)
will remove the empty fields by using the space character itself as a separator. This would give several more blank cells except for the final parameter that causes them to be removed.
If the single string were to become a list of strings you would hit the dreaded 'array of arrays' error in which Excel classes the correct solution to the problem as unsupported. If you could guarantee that all the substrings were 5 characters long then
= LET(
concat, MAP(list, LAMBDA(term, CONCAT(TEXTSPLIT(term, {"/"," "}, , TRUE)))),
MID(concat, {1,6,11}, 5)
)
should work.
May 04 2024 06:30 PM
=textsplit(WEBSERVICE("https://e.anyoupin.cn/eh3/?preg_match_all_join~\d+~" & A2),",")
May 03 2024 12:03 PM - edited May 03 2024 12:14 PM
SolutionYou could also employ =CHOOSECOLS(TEXTSPLIT(A1,"/"),1,4) if you have a new version of Excel or subscription to Microsoft 365. This formula has the advantage of working even if the strings of digits vary in length. (If the number of slash marks change, you will need to adjust up or down the final digit to reflect that.)