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

dav567

New Member
Joined
Jul 18, 2014
Messages
9
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!!
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Edit: Redoing formula. I misunderstood your post.
 
Upvote 0
This work for you?


Excel 2012
DEFGH
2501.90713017003017/501-9071
3501.0637001000001/501-0637
4501.0637770000770/501-0637
5501.0690001000001/501-0690
Sheet1
Cell Formulas
RangeFormula
H2=TEXT(F2,"000000")&"/501-"&TEXT(RIGHT(D2,4),"0000")
 
Upvote 0
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.
 
Last edited:
Upvote 0
Here's a bit more modular of a solution in case you have differing prefixes in column D:


Excel 2012
DEFGH
2501.90713017003017/501-9071
3501.0637001000001/501-0637
4501.0637770000770/501-0637
5501.0690001000001/501-0690
Sheet1
Cell Formulas
RangeFormula
H2=TEXT(F2,"000000")&"/" & INT(D2) & "-" & TEXT((D2-INT(D2))*10000,"0000")
 
Upvote 0
Oh crap that works! And you did it in seconds, wtf!? Thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu!!!
 
Upvote 0
You too! When I'm making bank I wanna come back on here and PayPal you for saving me frustration lol :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top