Excel : Need to add two digits to every line in a column

[liquidstayce]

I have a file for work that has a column with patient medical record numbers. They look like this "M123456"
I need to just add two random digits to that number for the medical record number for every row. (aka - ghetto encryption)

Oh excel gurus.. How do I do this automatically? Manually entering the two extra numbers is out of the question!

Thanks!

[Tikker]

hrm

It'd be much easier to pull it out of excel, use perl or awk against it, then import it back into excel

otherwise,
create another column somewhere with the random letter sequence you want (not sure the best way to do that) then just concatenate the 2 cells together

[liquidstayce]

I think something like =RANDBETWEEN(1,100) comes into play but I'm not sure how to do the rest of the formula.. like what exactly do I do to make sure it adds it to every number in to column without messing up the numbers before it? Thanks

[liquidstayce]

hrm

It'd be much easier to pull it out of excel, use perl or awk against it, then import it back into excel

otherwise,
create another column somewhere with the random letter sequence you want (not sure the best way to do that) then just concatenate the 2 cells together

well forget perl or awk.. excel is all I have so your third idea would probably work but what do I use to make the random two digits in the new column? Then do I just merge the cells? How do I concatenate in excel... I can do string manipulation and all sorts of fun stuff in my proprietary healthcare IS programming language but I'm clueless in excel.

[liquidstayce]

p.s. 5000 goat points to the person that figures this out first

[Tikker]

eh?

I gave you the answer already =P



Column1

M001
m002
m003
etc


Column2
=randbetween(1,100)
then drag it down to match the number of records



Column3

=concatenate(a1,b1) (or whatever the matching cell references are for the records, and the random number)


drag it down, you're done

[Ogru]

I haven't figured out a way to add numbers on the end, however, I did figure out how to add a random number to the beginning of a string.

=INT(RAND()*2000)&"M223456"

would do a random number between 1 and 2000, then add the M223456 text after it.

Hope this helps you.

[Gidan]

You can try this.

Lets say the value that you want to add 2 random digits to is in A1

=A1&DEC2HEX(RANDBETWEEN(0,256),2)

If you but that in B1. Then B1 will = A1 with 2 random hex digits at the end. Keep in mind those 4 digits would change every time you updated any field in the spreadsheet, thats just how rand woks.

[Tikker]

random sucks mad cock
and i want my 5000 goat pts

[liquidstayce]

you get 2500 because only half your solution worked. I couldn't get the concatenate to work.

Gid - I couldn't get yours to work either but its probably just me doing something wrong. My numbers are set up in Column A - each a different cell. Adjust the formula for that and test it to see if it works and you can have the rest of my goat points =)

M123455
M234233
M984321

[Gidan]

Ok Excel should look something like this


(A1) M123455 (B1) =A1&DEC2HEX(RANDBETWEEN(0,256),2)
(A2) M234233 (B2) =A2&DEC2HEX(RANDBETWEEN(0,256),2)
(A3) M984321 (B3) =A3&DEC2HEX(RANDBETWEEN(0,256),2)

Make sure you have the analys pack addon installed or the dec2hex function may not work.

if it still doesn't work, what version of excel are you useing?

[Tikker]

you get 2500 because only half your solution worked. I couldn't get the concatenate to work.

Gid - I couldn't get yours to work either but its probably just me doing something wrong. My numbers are set up in Column A - each a different cell. Adjust the formula for that and test it to see if it works and you can have the rest of my goat points =)

M123455
M234233
M984321

wtf, no gyping me just cause you suck at excel!

try this

in cell A1 type "m123" (no quotes obviously)
in cell b1 type "456"

then in c1 type "=concatenate(a1,b1)"

[liquidstayce]

I used this:
=CONCATENATE(A1,RIGHT((ROUND((RAND()*100),0)+10),2))


Thanks for all the help. Gid you can still have my other goats!!