Excel is returning #VALUE! errors when using the LEFT function - Microsoft Community

here formula trying use:

=if(counta(d6),text(row(a5),"pcr"&"170000"&left(d6))," ")

so if there data in column d, sequence serial number cell a5 preceding "pcr" adding sequence of 170000, add left single character column d end.  

final result should be:

"pcr170001a" if column d's value abc

"pcr170002b" if column d's value bet

this formula works every other letter of alphabet except ones listed in subject line.  i beyond limit trying find answer.  

i tested independently using "left(d6)" , returned correct value, formatted as text cell.  

i tested formula within ms excel online, same result.

any appreciated!

hi,

does help?

4
			bet	pcr17001b	=if(d6="","","pcr"&rows($5:5)+17000 &left(d6))
			abc	pcr17002a

Office / Excel / Other/unknown / Office 365 for business