How to replace last character of a cell

[taylorpeter55]

Hi all,
I’ve need to achieve something which on the face of it seems relatively straightforward but i’m having issues identifying a working solution.

In a column, I have a list of page numbers e.g. page 1, page2, page 3a, page 3b page 4b, page 5, page 53a…

What I need to do is replace the trailing a (& not the a in the word page) with the string “-1”. Similarly, i need to replace the trailing b with the string “-2”.

I’ve tried a few different regex but i can’t get any to work.

Does anyone have an idea of how i can do this please?

Thanks in advance.
Pete

[sanket_2012]

Hello @taylorpeter55 ,
Can you try this substr($Name$,0,length($Name$)-1 ) in the string manipulation node?

-1 for for Current column and -2 for Required column

Let us know if it works

Thanks,
Sanket

[taylorpeter55]

Hi Sanket,

Thanks for your reply.

I’ve used your proposed expression:

You can see the output of the node in the screenshot below:

The last character of the Page number has been removed from all rows.

The desired output is for the trailing letter a (e.g. 52a) is for it to be replaced by the string “-1”. i.e. Page Number New becomes 52-1.

The desired output is for the trailing letter b (e.g 52b) is for it to be replaced by the string “-1”. i.e. Page Number New becomes 52-2.

Thanks in advance for your help.
Pete

[takbb]

Hi @taylorpeter55 ,

Extending @sanket_2012 's proposal, try the following in String Manipulation:

string(
regexMatcher($column1$,"page [0-9]+[a-z]+")=="True"
?join(substr($column1$,0,length($column1$)-1), string(-1 * (1+indexOf("abcde",substr(reverse($column1$),0,1)  ))))
:$column1$
)

This uses regexMatcher to determine if the page reference is of the form page nx, such that n is numeric and x is alphabetic.

If that returns “True”, it uses the code from @sanket_2012 to remove the last letter, and replaces it with a numeric representing that letter (multiplied by -1), so a=>-1, b=>-2, c=>-3 and so on

In the above I’ve assumed you don’t go above “e”, but if you do, just add further letters to the “abcde” string, all the way up to “z” if need be.

edit: if you want to avoid the “ternary operator” conditional syntax of ? and : and the substr, here is an alternative:

regexReplace(
    $column1$,
    "(page [0-9]+)([a-z]+)",
    "$1-"+
        string(
        	 1+indexOf("abcdefghijklmnopqrstuvwxyz",
        	    regexReplace($column1$,"(page [0-9]+)([a-z]+)","$2"))
        	   )
    )

[taylorpeter55]

Hi @takbb,

Thanks so much for this solution - it works brilliantly

Much appreciated.

Thanks.
Pete