change last digit in cell if it is a zero

[Gastank]

Hi,

looking for help please,

In Excel 2010 I am generating a random 5 digit number in cell A1 and I am using the last digit to select the corresponding letter from a word in another cell.

e.g. A1 = 45678 - last digit = 8 corresponding letter from the word "Subscribed" = b

however if A1 = 45670 - last digit = 0 then I receive an error message in my letter selection cell.
Is there a way to automatically change last digit in Cell A1 if it is a zero to any number between 1 and 9.

thanks in advance for any help.

 

[Snakehips]

Gastank,
Do you want the number in A to change to show the substituted last digit?

What s generating the random number, formula or code? Can you post?

Or do you just want the letter generating formula to ignore the error and give you a letter?

 

[lrobbo314]

I have the random number in A1 and the word in E1.

This formula should do what you are asking for.

=IF(RIGHT(A1,1)+0=0,MID(E1,RANDBETWEEN(1,9),1),MID(E1,RIGHT(A1,1),1))

 

[Gastank]

Gastank,
Do you want the number in A to change to show the substituted last digit?

What s generating the random number, formula or code? Can you post?

Or do you just want the letter generating formula to ignore the error and give you a letter?

Hi Snakehips

I would like the number in A to change from say 46780 to 46781 or 2 etc., I'm generating the random number formula thus (=RANDBETWEEN($B$1,$B$2))

thanks

 

[Momentman]

Maybe
=IF(1*RIGHT(A1,1)=0,0+(LEFT(A1,4)&RANDBETWEEN(1,9)))

4 because you have 5 digits or you could use

=IF(1*RIGHT(A1,1)=0,0+(LEFT(A1,LEN(A1)-1)&RANDBETWEEN(1,9)))

 

[Snakehips]

Gastank,

A couple of possibilities as I see it.

As I think Momentman eludes to....
Have the RANDBETWEEN formula in any spare available cell (maybe in a column you can hide if you wish?) In below example this cell is C1
Then have the below formula in A1 which modifies the number in C1 as and when required.

Excel Workbook
	A	B	C
1	7212	1000	7210
2		50000
Sheet18

If you are ok to use some vba then you can do without the helper cell and use a formula direct in A1.
Open the vba editor select your project and insert a code module.
Then paste the below UDF code into the module.

Function MyRandBet(Lowr, Highr As Range)
If Lowr = "" Then MyRandBet = 1 / 0
L = Lowr - (Right(Lowr, 1) = "0" * 1)
H = Highr + (Right(Highr, 1) = "0" * 1)
MyNum = 0
Do Until Right(MyNum, 1) <> "0"
MyNum = WorksheetFunction.RandBetween(L, H)
c = c + 1
If c = 10 Then Exit Do
Loop


MyRandBet = MyNum
End Function

You can then have the following formula in A1 and will not get any last digits as 0.

Excel Workbook
	A	B
1	30378	1000
2		50000
Sheet18

Hope that helps.

 

[mikerickson]

How about = 10*RANDBETWEEN(1000,9999) + RANDBETWEEN(1,9)

Or if you already have a random number in A1 = FLOOR(A1,10)+RandBetween(1,9)

 

[Snakehips]

How about = 10*RANDBETWEEN(1000,9999) + RANDBETWEEN(1,9)

Mike, that could well be the one.

Thrown by the fact that OP is referencing B1 & B2 for the rand limits, which I assumed he may vary, I totally lost sight of the fact that he is only wanting to generate a 5 digit number.

@Gastank ??

 

[mikerickson]

It may need some tweaking if the OP considers "01234" a five digit number.

 

[Gastank]

thanks everyone for your suggestions, I'll start tweaking them now and I am sure I will now be working in the right direction.

thanks again