cancel
Showing results for 
Search instead for 
Did you mean: 

Remove Leading Zeros from Text

RSHERRY
Staff
Staff

Hi 

 I am extracting text from Excel which contains leading zeros as well as zeros with the in the text. 

I would like to remove the leading zeros only. 

Example  : 00987098  = 987098

I have looked at regex but it removes all the zeros, is there an easier way to do this ?

Many thanks



------------------------------
Rachael Sherry
------------------------------
1 BEST ANSWER

Helpful Answers

MichealCharron
Level 8

While I would probably cast the string to a number, you could also use the "Regex Replace" action in Blue Prism's "Utility - Strings" VBO with the following parameters:

Pattern: "0*([1-9]\d*)|0"
Replacement Data: "$1"

29738.png
29739.png

The only advantage over casting is that it will return a blank value if the string is all zeros.



------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

Micheal Charron
RBC
Toronto, Ontario
Canada

View answer in original post

7 REPLIES 7

You can try saving the Data Item in Number Type

29730.png



------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

Thanks I will try that



------------------------------
Rachael Sherry
------------------------------

Hi Rachael,

As the data comes from XL, you could consider changing the data type for that XL column to Number, which would remove leading zeroes. Then you extract the data to your process.



------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------
Happy coding!
Paul, Sweden
(By all means, do not mark this as the best answer!)

JD_CPU
Level 4

Hello Rachael,

Right(00987098,6) works on text in Blue Prism. As in Right([OriginalText],6).. But of course you could only use this approach if you knew you always wanted the first 6 characters as read from right.

Otherwise, you would have to use some additional type of logic (like this choice stage) to determine how many characters from the right that you want to keep.

29733.png

29734.png

The calculation stage below each choice would look like this.

29735.png

The calculation stage above the choice just captures the length of the OriginalText to enable the math in the Right() function.

29736.png



------------------------------
Joseph Rivera
------------------------------

MichealCharron
Level 8

While I would probably cast the string to a number, you could also use the "Regex Replace" action in Blue Prism's "Utility - Strings" VBO with the following parameters:

Pattern: "0*([1-9]\d*)|0"
Replacement Data: "$1"

29738.png
29739.png

The only advantage over casting is that it will return a blank value if the string is all zeros.



------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

Micheal Charron
RBC
Toronto, Ontario
Canada

Hi Rachael,

There are lot many ways to replace the leading zeros. But I would suggest the best way to remove leading zeros in excel it self.

You can convert the the excel to general or number format it will remove leading zeros. 

2nd way you can copy that columns and paste as values in another column then leading zeros will go away. Then you take it to collection.



------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------
Regards,
Amlan Sahoo

Thanks Michael , I have tried this and it works prefectly. 



------------------------------
Rachael Sherry
------------------------------