How can I make the RIGHT(text, [num_chars]) function return a 0 that is the last digit?

[dav567]

I've taken forever developing this formula that you guys can probably come up with in 2 minutes. Here it is:

=RIGHT("000"&F2,6)&"/"&"501-"&RIGHT(D2,4)

What it does is create a combined string using the first two columns. The string has to be in this format ######/501-#### but as you can see in the last one the JOB # has a 0 in the end and the COMBINED STRING formula messes up.


JOB #_______FUND____COMBINED STRING

501.9071_____3017____003017/501-9071
501.0637______001____000001/501-0637
501.0637______770____000770/501-0637
501.0690______001____000001/501-.069

Please help!!

 

[MrKowz]

Edit: Redoing formula. I misunderstood your post.

 

[MrKowz]

This work for you?

Excel 2012
	D	E	F	G	H
2	501.9071		3017		003017/501-9071
3	501.0637		001		000001/501-0637
4	501.0637		770		000770/501-0637
5	501.0690		001		000001/501-0690
Sheet1

Cell Formulas
Range	Formula
H2	=TEXT(F2,"000000")&"/501-"&TEXT(RIGHT(D2,4),"0000")

 

[dav567]

Yea that returned the first half right but the second part messed up. That first part is simpler the way you formatted it, thanks.

Wait, why is it working in your example, but not in mine? I'm checking again.. I'm getting 000001/501-0000 for that last one.

 

[MrKowz]

Here's a bit more modular of a solution in case you have differing prefixes in column D:

Excel 2012
	D	E	F	G	H
2	501.9071		3017		003017/501-9071
3	501.0637		001		000001/501-0637
4	501.0637		770		000770/501-0637
5	501.0690		001		000001/501-0690
Sheet1

Cell Formulas
Range	Formula
H2	=TEXT(F2,"000000")&"/" & INT(D2) & "-" & TEXT((D2-INT(D2))*10000,"0000")

 

[dav567]

Oh crap that works! And you did it in seconds, wtf!? Thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu!!!

 

[MrKowz]

Oh crap that works! And you did it in seconds, wtf!? Thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu!!!

Happy to be of assistance. Have a good weekend!

 

[dav567]

You too! When I'm making bank I wanna come back on here and PayPal you for saving me frustration lol

 

[MrKowz]

You too! When I'm making bank I wanna come back on here and PayPal you for saving me frustration lol

HAHA! Accepting payment is against forum policy. Enjoy your stay here at the MrExcel forums. Tons of gurus that put me to shame lurk about, and they're all willing to assist.