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

liquidstayce · Post by **liquidstayce** » Mon Jun 20, 2005 2:01 pm

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 · Post by **Tikker** » Mon Jun 20, 2005 2:08 pm

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 · Post by **liquidstayce** » Mon Jun 20, 2005 2:08 pm

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 · Post by **liquidstayce** » Mon Jun 20, 2005 2:21 pm

Tikker wrote: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 · Post by **liquidstayce** » Mon Jun 20, 2005 2:23 pm

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

Tikker · Post by **Tikker** » Mon Jun 20, 2005 2:36 pm

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 · Post by **Ogru** » Mon Jun 20, 2005 2:39 pm

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.

Post by **Gidan** » Mon Jun 20, 2005 3:25 pm

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 · Post by **Tikker** » Mon Jun 20, 2005 4:09 pm

random sucks mad cock
and i want my 5000 goat pts

liquidstayce · Post by **liquidstayce** » Tue Jun 21, 2005 6:10 am

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

Post by **Gidan** » Tue Jun 21, 2005 11:35 am

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 · Post by **Tikker** » Tue Jun 21, 2005 12:17 pm

liquidstayce wrote: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 · Post by **liquidstayce** » Wed Jun 22, 2005 7:17 am

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

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