Need help with text to column like formulas

[SteveM99]

We have a budget file where users are entering periods in one column, ie. 1,2,5 etc (Jan, Feb, May). In columns to the right, columns referred to as 1 to 12 (these are the period) we need the numbers to get into those cells specific to those columns only. For example... 1 would show up in column 1, 2 in col 2, 5 in column 5 etc. Not too hard except when we get to 10, 11, 12 as these create issues for columns 1, 2, 10, 11, and 12.

Attached is the excel file for visual review. We would prefer a non vba solution. I spent about 3 hours researching a variety of nested if thens with mid, substitute, find etc. Got stuck in weeds on this. Please help with suggestions or experiences with splitting out numbers 1-12 that deal with 1,2,10,11,12 dilemma.

[arnelgp]

see the Formula.

[SteveM99]

Holy fpoop! I can't believe how you got this so quick. Trying to understand the formula and I am lost with all the "," in it. Seems like you are dealing with the commas in the months but I don't get the sequence. If possible can you describe, if not its okay thank you for answer! Made my Friday, I can continue coding the rest of my work.

[arnelgp]

First what does Find() function does:


The Excel FIND function returns the position (as a number) of one text string inside another.
When the text is not found, FIND returns a #VALUE error.
(https://exceljet.net/excel-functions/excel-find-function)


Find("whatToSearch", "whereToSearch")


What i did is i need to Find the Text of my Header column (starts on Row 4)
to the values on Column D.


Let's take Row 5 as an Example:
On Each "whatToSearch" (the Header) text, I added Comma (,) before and after.
So, the number "1" now becomes ",1,".
I did the same with the "whereToSearch" part (Column D).
So "1,2,3" after prefixing and suffixing it with "," it now
becomes ",1,2,3,".

Now, you can Uniquely identify each column header:
",1,"
",10,"
etc.


I wrap the function in IsError() so that when the Find did
not find the search string, instead of displaying error it will
display "" (blank). Otherwise display the character "x".