Need some help writing a recursive function

hi, thanks for you help! my background is in compiled languages (C, C++ etc) and I am new to powershell. i need to write a recursive function but am having issues with the syntax. this is my code:

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ($ColumnNumber -gt 26){
        return [char]([int][Math]::Floor($ColumnNumber,26))+myfunction($ColumnNumber-26)
    }
    return [char]$ColumnNumber

}

when i try to run this, i get the following:

At C:\Users\tonyg\Google Drive\Work\PowerShell\Untitled3.ps1:11 char:39
+         $ColumnString = $ColumnString+myfunction($ColumnN ...
+                                       ~
You must provide a value expression following the '+' operator.
At C:\Users\tonyg\Google Drive\Work\PowerShell\Untitled3.ps1:11 char:39
+         $ColumnString = $ColumnString+myfunction($ColumnN ...
+                                       ~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'myfunction' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ExpectedValueExpression

so, i see it does not like me calling my function on the right side of the “=”. i have looked around and the examples of recursion i see on the internet are not calling the function this way.

is there a way to do what i am trying to do or is there a “powershell” way to do this?

thanks again

Tony
welcome to the forums.

What should be the actual purpose of your function? Did you notice that your error message does actually not fit to the code you posted? At least the line in the error message does not exist in your function.
In PowerShell we call functions like this:

myfunction -ColumnNumber 234

… no parenthesis needed. :wink:

Another issue might occur when your function returns a [char] but it expects an [int] as input. I wouldn’t expect this to run in a recursive function.

Next … if I’m not wrong [MATH]::(Floor) only takes one argument. I’d expect you to get more than just the error you posted.

Hi, sorry fort he sloppy post, let me clarify.

  1. sorry for the typo, this is what i meant [char]([int][Math]::Floor($ColumnNumber/26)) …on this topic, this is the only way i could find to get the “div” of two number…i find it surprising that powershell has a mod (modulo) function 5%2=1 but no div…ie 5div2=2 ? am i missing something?
  2. but on your main point, the error was a different issue. with the code below, i get no errors, however the function returns nothing…i was expecting a string of [char] to be built up in the iterations.

the purpose of this code is to translate worksheet column numbers into column letters…like column 27 is column AA etc.

updated code

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ($ColumnNumber -gt 26){
        return [char]([int][Math]::Floor($ColumnNumber/26))+[char](myfunction($ColumnNumber-26))
    }
    return [char]$ColumnNumber

}

$txt = myfunction 14

write-host $txt

PowerShell is made for administrators - not for programmers. :wink: Why make it harderr than necessary to divide 2 numbers? Simply write it as you would on paper. 5/2 and the result will be 2.5. Neat, huh? :wink: :smiley:

Hmmm … I still did not get it. You may post some sample input and the desired output.

With the cast operator [char] you convert a number to its charachter equivalent. But that actually starts to make sense with numbers like 33 and above. Below that you only get non printable charachters backspace, line feed, horizontal tab, form feed, escape and so on.

And there still the issue I already mentioned in my first answer. If you want to recursively call you function the return value should at least be of the same type as the expected input vaule. :wink: :wink: … I’d expect that schould be the same in C or C++.

Olaf, i don’t think you understand the difference between div and divide. 5/2=2.5 5div2=2…i need div, not divide.

on your main point, i think i realized the same thing, i needed to add 65 to the [char] to get A.
my code is working better now, however, it is not complete…for numbers > 52, it is broken, working on that now.

updated code:

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ($ColumnNumber -gt 26){
        return [char](([int][Math]::Floor(($ColumnNumber-1)/26)+64))+[char](myfunction($ColumnNumber-26))
    }
    return [char](($ColumnNumber)+64)

}

Now I got it. Thanks for the clarification. Anyway you already found a way … [int][Math]::Floor(5 / 2) … of course PowerShell does not provide that much builtin functions like general purpose programming languages. You may find more info here:

I start to have an idea what you’re trying to do. I just don’t get what it is with the number 26 in your code. Could you please provide some sample input and the according desired output?

Thanks in advance.

26 letters in the English alphabet, [char]65 = A

myfunction 26 should = Z
myfunction 52 should = AZ

yes, but is is odd that it has modulo but not div…they go together
5/2 = 2.5
5%2=.5 (mod or modulo)

5div2=2

Funny. I’d expect for the input of 52 actually ‘ZZ’

Since PowerShell is open source you may ask for this function on Github. :wink:

That’s actually not a very helpful description. If you get errors you should post them here (formatted as code please)

It works like that but I’m not sure if it is what you expect …

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ($ColumnNumber -gt 26) {
        return [char[]]([int][Math]::Floor(($ColumnNumber - 1) / 26 + 64)  ) + [char[]]((myfunction -ColumnNumber ($ColumnNumber - 26))) -join ''
    }
    return [char](($ColumnNumber) + 64)
}

Edit:
I just noticed now while playing a little bit - it starts to get funny again on numbers greater than 702.

PS D:\sample> myfunction 702

ZYXWVUTSRQPONMLKJIHGFEDCBAZ
PS D:\sample> myfunction 703

[ZYXWVUTSRQPONMLKJIHGFEDCBAA

ahhh, -join are you doing that to return a string instead of a char…perfect. i was just sitting down to figure that out…i was going to try and cast it as [string] after casting it as [char]

i will look this over…

hey, thanks for you time on this…

oh but this isn’t right…yes, i have not been very descriptive…in a spreadsheet, the columns are lettered A → blah but a lot of excel powershell functions return column number (not the letter) so i have the need to convert the column number to it letter…so column 26 is Z, column 27 would be AA column 52 would be AZ etc. column 676 would be ZZ…so i think there is a flaw in my recursion…that is what i will look at next.

thanks again

That is the understatement of the century. :roll_eyes:

That should have been your subject!!!

I’ll go to bed now. I may think about this tomorrow.

Rather than write your own function, can you use the ImportExcel module? It already has this functionality.

PS E:\Temp> Get-ExcelColumnName 52

ColumnNumber ColumnName
------------ ----------
          52 AZ
1 Like

Thank you very much. I was thinking about that module. I just didn’t know the name of the function.
Thank you.

hi, i am so glad you brought this up. i was aware of this module, however, i can find no real documentation with it (aside from the one video the author provides). all the examples of this are how to create spreadsheets from data, but none highlight the other functionality…like Get-ExcelColumnName.
do you know where this other functionality is documented?

btw, i looked at the source for Get-ExcelColumnName and now, i want to complete this using recursion as a a matter of pride…(not a good reason but…)

ok, so i worked on my code and i have made a lot of progress… but i am having a slightly different issue.

now, myfunction 52 produces A A (note the space in the middle) i need it to return AA (no space)…i am not sure where that space comes from.

new code:

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ([math]::Log($ColumnNumber,26) -le 1){
        return [string]([char](($ColumnNumber)+64))
    }
    else{
        return [string]([char[]](myfunction -ColumnNumber ([int][Math]::Floor(($ColumnNumber-1)/26)))+[char]((($ColumnNumber-1)%26)+65))
    }
}

#for ($i=1; $i -le 100; $i++){
#$txt = myfunction $i
#write-host "I is $i, Column is -$txt-"
#}

$txt = myfunction 703
write-host $txt

Try

[CHAR[]](34,35,65)

:wink:

… now try

[CHAR[]](34,35,65) -join ''

:wink:

PERFECT!

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ([math]::Log($ColumnNumber,26) -le 1){
        return [string]([char](($ColumnNumber)+64))
    }
    else{
        return [string]([char[]](myfunction -ColumnNumber ([int][Math]::Floor(($ColumnNumber-1)/26)))+[char]((($ColumnNumber-1)%26)+65) -join '')
    }
}

#for ($i=1; $i -le 100; $i++){
#$txt = myfunction $i
#write-host "I is $i, Column is -$txt-"
#}

$txt = myfunction 703
write-host $txt

Olaf, thanks for all your help, the function seems to work well, here is the final code.

thanks again

function myfunction {
    param(
        [Parameter(Mandatory = $true)]
        [int]
        $ColumnNumber
    )

    if ([math]::Log($ColumnNumber,26) -le 1){
        return [string]([char](($ColumnNumber)+64))
    }
    else{
        return [string]([char[]](myfunction -ColumnNumber ([int][Math]::Floor(($ColumnNumber-1)/26)))+[char]((($ColumnNumber-1)%26)+65) -join '')
    }
}

Good work finishing your function. It works well :smiley:

Documentation and even the comment-based help is lacking. I’m sure the project would welcome contributers to improve the situation.
Once I saw what you were trying to do, I figured the module might already have the functionality so I just did Get-Command -Module ImportExcel and looked for something relevant.

1 Like