Pull Info After a Name in a Cell

[stanleytheyak]

I have a list of numbers that I need to pull out and turn into a 6 digit identifier. The numbers are in varying formats, as you can see below. I need to take the number and make it a 6 digit identifier with 0's filling in any blanks.

So, I have this:
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7538</td> <td style="text-align: right;">36634</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7539</td> <td style="text-align: right;">6729</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7540</td> <td style="text-align: right;">2353</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7541</td> <td>A000066755</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7542</td> <td style="text-align: right;">46469</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7543</td> <td style="text-align: right;">3633</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7544</td> <td style="text-align: right;">38073</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7545</td> <td style="text-align: right;">4591</td></tr></tbody></table>
and I need this:
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>F</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7538</td> <td style="text-align: right;">036634</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7539</td> <td style="text-align: right;">006729</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7540</td> <td style="text-align: right;">002353</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7541</td> <td style="text-align: right;">066755</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7542</td> <td style="text-align: right;">046469</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7543</td> <td style="text-align: right;">003633</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7544</td> <td style="text-align: right;">038073</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7545</td> <td style="text-align: right;">004591</td></tr></tbody></table>
without having to do it manually.

Any help is appreciated.

 

[njimack]

=if(len(e7538)>=6,right(e7538,6),rept("0",6-len(e7538))&e7538)

 

[Andrew Poulsom]

Try:

=TEXT(RIGHT(E7538,6),"000000")

 

[stanleytheyak]

Andrew, that works perfectly! Thank you!