I have an enumlist with months ex. (Jan, Feb, Jun, Aug). I need a formula to convert them to a list of their numeric equivalents (1,2,6,9). Does anyone know of an elegant way to do this?
Hi @Glenn_Thorne Check out SWITCH or SUBSTITUTE
SWITCH([Month],
“January”, 1,
“February”, 2,
etc…
Yes, I get that but how to process all items in the list into another list??? Or can this work?
[MonthNum] = SWITCH([Month],
“January”, 1,
“February”, 2,
etc…
Is MonthNum a list of month numbers?
Yessir it is
No easy elegant fix here, you’ll have to construct some formula to reconstruct the list.
substitute(substitute([MonthNum], 1, “January”), 2, “February”), etc
This is close. If I understand it properly, it will change the items in the current list rather than making a new list though. I never thought this would be so difficult actually lol
Completing this out, and putting {1,2,3,4,5,6,7,8,9,10,11,12} for a sample list (giving one example of every month) you get the following:
substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute({1,2,3,4,5,6,7,8,9,10,11,12}, 12, “December”), 11, “November”), 10, “October”), 9, “September”), 8, “August”), 7, “July”), 6, “June”), 5, “May”), 4, “April”), 3, “March”), 2, “February”), 1, “January”)
I just did a test, to see if the substitute would preserve the list nature of things: yes it did.
thinking this but seems to have a syntax error…
switch([Corporate Remit Months]
,1, “January”
,2, “February”
,3, “March”
,4, “April”
,5, “May”
,6, “June”
,7, “July”
,8, “August”
,9, “September”
,10, “October”
,11, “November”
,12, “December”
, “Error”)
is that a list or a number? You can’t use a list in a switch like this.
its a list. I want to convert a list of month numbers to equivalent list of month text’s
substitute
Now im lost
Maybe take a look at this help document? Check the “Month number to Name section”? https://help.appsheet.com/en/articles/2357278-index
Thats a great article Mike and will work for concerting one month number to text but I dont think it sheds light on how to concert a list of month numbers to month tests. I am doubting that it can be done.
g
Ah… different use case. Could do on the back end (if the list is in google sheets). Otherwise, the formula guru (@Steve) might have a suggestion.
Do you know the formula in google sheets?
If A1 has the month name in it, you could use something like this in cell B1:
=ARRAYFORMULA(MONTH(A1:A&1))
nope, cuz A1 would have a list in it like 1,2,5 for example lol
or the other way around:
@Glenn_Thorne
You can try with this:
CONCATENATE(
SPLIT(
IFS(
IN(1, [ENUMLIST]),"January,"
)&
IFS(
IN(2, [ENUMLIST]),"February,"
)&
IFS(
IN(3, [ENUMLIST]),"March,"
)&
IFS(
IN(4, [ENUMLIST]),"April,"
)&
IFS(
IN(5, [ENUMLIST]),"May,"
)&
IFS(
IN(6, [ENUMLIST]),"June,"
)&
IFS(
IN(7, [ENUMLIST]),"July,"
)&
IFS(
IN(8, [ENUMLIST]),"August,"
)&
IFS(
IN(9, [ENUMLIST]),"September,"
)&
IFS(
IN(10, [ENUMLIST]),"October,"
)&
IFS(
IN(11, [ENUMLIST]),"November,"
)&
IFS(
IN(12, [ENUMLIST]),"December,"
),
","
)
)
Went with Substtiute but I think this would work as well.
thx much
Bingo! Got this to work! Thanks much!!
User | Count |
---|---|
26 | |
25 | |
25 | |
20 | |
19 |