An addendum to point 3 and making the Account Numbers a standard length. This can be done very easily beforehand in Excel too; it requires 2 just steps.

Using your same example data - where the longest full Account number is 10 digits long, this would be the process. Obviously it'll need adapting to whatever the true longest number is.

**Step 1**: use CONCATENATE to add some preceding 0s so that there are sufficient in place. Given the standard length here will be 10, then adding 9x 0s should suffice.

Again assuming the data is in column A, one method of writing the formula is

=CONCATENATE("000000000",A1)

**Step 2**: use RIGHT to extract the last 10 digits from the new column B. I.e.

=RIGHT(B1,10)

Using your data again, it looks like this

000123456 |
000000000000123456 |
0000123456 |

001234560 |
000000000001234560 |
0001234560 |

00000120456 |
00000000000000120456 |
0000120456 |

001230004587 |
000000000001230004587 |
1230004587 |

0006540002455 |
0000000000006540002455 |
6540002455 |

00012540000 |
00000000000012540000 |
0012540000 |

------------------------------

Anthony Patamia

------------------------------

Original Message:

Sent: Thu April 22, 2021 04:37 AM

From: Anthony Patamia

Subject: Remove zeros on the left

Morning all

Having read the replies from Mark & Linda, here are a couple of extra thoughts.

1) Mark is correct that running

*Remove prefix "0"*

*Remove prefix "00"*

*Remove prefix "000"*

*Remove prefix "0000"*

*Remove prefix "00000"*

will not work properly, but flipping the arguments round so they are

*Remove prefix "00000"*

*Remove prefix "0000"*

*Remove prefix "000"*

*Remove prefix "00"*

*Remove prefix "0"*

should do the job as it will remove the largest first.

2) Linda suggests cleaning it up in the spreadsheet first. Much as I personally like the Importer tools in ANB, when training ANB I do recommend to the delegates to get it looking how they want in Excel first. This is because quite a few users are intimidated by an Import proces, and therefore by having it looking right in Excel it is one less thing that can feasibly go wrong during an Import.

The issue you will have is that the account numbers all have a different number of digits (*but see point 3 below*), so you cannot use a RIGHT function alone; and because 0s appear within the main account number you cannot use a SUBSTITUTE function by itself.

There is a nested formula you could write, which would need:

- An IF function at the start, to determine whether the first character in the text string is a '0'. This will need to nest...

- A LEFT function to determine if the string starts with a '0'

Then if True...

- apply a SUBSTITUTE function to remove just the first "0" from the text string. This will prevent 0s in the middle of the account number being deleted

Then if False

- just make it the same as the original cell.

Assuming the list of account numbers is in Column A, this is the formula for the first row

=IF(LEFT(A1,1)="0",SUBSTITUTE(A1,"0","",1),A1)

Now you would need to run this multiple times to remove all the 0's, but that's simple because you'll note I didn't use an absolute reference when pointing to cell A1 (*i.e. not $A$1*). You just drag the formula down, and then to the right until all the 00s have gone.

I did this with your examples, and the 0s were all gone by column F. Copy > Paste Values to your import table and job done.

000123456 |
00123456 |
0123456 |
123456 |
123456 |
123456 |

001234560 |
01234560 |
1234560 |
1234560 |
1234560 |
1234560 |

00000120456 |
0000120456 |
000120456 |
00120456 |
0120456 |
120456 |

001230004587 |
01230004587 |
1230004587 |
1230004587 |
1230004587 |
1230004587 |

0006540002455 |
006540002455 |
06540002455 |
6540002455 |
6540002455 |
6540002455 |

00012540000 |
0012540000 |
012540000 |
12540000 |
12540000 |
12540000 |

3) Perhaps tackle this a different way. Why not have a standard text string length of Account number, and only delete the 0's that precede that. The longest number in your dummy data is 10 digits long once you crop the leading 0s. This way you can use an =RIGHT([*cell ref*],10) formula.

We do this on for a particular reference number in our iBase build; the original number can be 1-4 digits long, but needs to referenced by another lookup table, so we made them all 4 digits long and added preceding 0s to make the data consistent.

I hope one of these works for you

Ant

------------------------------

Anthony Patamia

Original Message:

Sent: Wed April 21, 2021 10:55 AM

From: Paulo Ribeiro

Subject: Remove zeros on the left

I have a problem that would like your help.I have a list of account numbers, which may vary in size, for example account No. 123456, No. 1254, No. 12545877.However, the file that has these data comes with account numbers, but has zeros on the left, which can vary in quantity, for example, it can come in this way, 000123456 or 00123456 or 0000123456.I would like to know how to remove these zeros on the left without having to use "remove prefix" as the number of zeros varies.I also have to be careful when removing these zeros as I have account numbers that in the middle can have zero, for example 0001230004

Some examples for acount numbers:

000123456

001234560

00000120456

001230004587

0006540002455

00012540000

Any suggestion

------------------------------

Paulo Ribeiro

------------------------------