Keep Leading Zeros

[Sufiyan97]

is there any way to keep leading zeros while using formula,
length of number is not same for all rows,
also leading zero number is not same for all some have one leading zero some have two and some have three,
is there a way to keep original number while grabbing it with formula?

Book2
	B	C	D	E	F	G	H	I	J
1	Employee Name	Priority	Bank Name	Account #
2	ALEXANDER, ANGELA L	1	BANK OF AMER 0196	000650342558		ALEXANDER, ANGELA L	650342558
3	COWAND, MICHAEL D	1	BB & T 1121	0005112406796		COWAND, MICHAEL D	5112406796
4	Williams, Leigh H	1	CAROLINA TELCO 4977	0002036349		Williams, Leigh H	2036349
5
6
7
8
Table 1

Cell Formulas
Range	Formula
H2:H4	H2	=LEFT(INDEX(E:E,MATCH(G2,B:B,0)),LEN(INDEX(E:E,MATCH(G2,B:B,0))))

 

[Fluff]

The numbers in col E do not have leading zeros, that is just a custom cell format.

 

[Joe4]

Just use the same custom format as you have in column E for column H.

 

[Sufiyan97]

Thank you both for looking into it.

I have checked and all cells have different custom format, is there any way to apply same format for each number based on column E?

 

[Fluff]

Not without some sort of logic.

 

[Joe4]

Thank you both for looking into it.

I have checked and all cells have different custom format, is there any way to apply same format for each number based on column E?

One easy way would be to simply copy the entire column format from column E and apply it to column H.
Just select column E, click on the Copy Format Painter, and then select column H and press enter.

In VBA code, it would simply look like:

Sub CopyFormattingMacro()

    Columns("E:E").Copy
    Columns("H:H").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub

 

[Sufiyan97]

Thanks Joe, I think what I was looking for has no logic, I was pulling some of the numbers based on name with formula and wanted to appy that format to only formula results.

Thank you again for your time and suggetions.

 

[Joe4]

Thanks Joe, I think what I was looking for has no logic, I was pulling some of the numbers based on name with formula and wanted to appy that format to only formula results.

Thank you again for your time and suggetions.

Note that native Excel functions only run on values. They do not look at the formats of other cells.
Formatting only changes the appearance on the screen for the user - it has no impact on the underlying values in the cells, and that is what native Excel formulas reference.

So, since there appears to be no consistency in the format of column E from cell-to-cell, I really only see two options:
1. Manually copy the formats for the cell/columns, like I explained
2. Use VBA to copy the formats from column E to column H (there are other ways of going about this that may be automated, depending on how data is being updated, and how the updates should be triggered).