General Ledger Code Format

[josros60]

Hi,

how can format gl account number in excel

when typing 60320028500500 to 603200-28-500-500


thanks,

 

[Toadstool]

Try a Custom Format type of 000000-00-000-000

 

[josros60]

I did that but my vlookup function will not work once i do that.

thanks.

 

[Toadstool]

The Format will only work if it's stored as a number and VLOOKUP will only work if both the lookup value and lookup array are the same format.

Enter =TYPE(A1) where A1 is the cell reference for the GL account number cell. It should show a 1 to indicate it's a number.
Now try the same for any of the cells against which your doing the VLOOKUP. I suspect it gives a 2 to indicate it's a text so VLOOKUP will never work.

 

[AhoyNC]

Maybe something like this. Assumes the GL numbers in the lookup table are text.

Book1
	A	B	C	D	E
1	GL	Data		Lookup GL	456216-12-555-689
2	456216-12-551-256	1		Data	2
3	456216-12-555-689	2
4	456161-25-556-229	3
5	589161-25-556-227	4
Sheet2

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(TEXT($E$1,"000000-00-000-000"),$A$2:$B$5,2,0)