Search code examples
excelfunctionexcel-2013

How to build complex value from three variables?

I have an Excel spreadsheet with over 2000 entries:

Field B1: CustomerID as 000012345
Field B2: CustomerID as 0000432

Field C1: CustomerCountry as DE
Field C2: CustomerCountry as IT

I need to build codes 13 digits long including "CustomerCountry" + "CustomerID" without leading 0 + random number (can be 6 digits, more or less, depends in length of CustomerID).

The results should be like this: D1 Code as DE12345967895 or D2 Code as IT43274837401

How to do it with Excel functions?

UPDATED: I tried this one. My big problem is to say that random number should be long enough to get 13 characters in all. Sometimes CustomerID is just 3 or 4 digits long, and concatenation of three variables can be just 10 or 9 characters. But codes have to be always 13 characters long.

enter image description here

Solution

  • Use & to concatenate strings.

    Use VALUE(CustomerID) to trim the leading zeroes from the ID

    Use RAND() to add a random number between 0 and 1 or RANDBETWEEN(x,y) to create one between x and y.

    Combine the above and there you are!

    If you always want 13 digits you can use LEFT(INT(RAND()*10^13);(13-LEN(CustomerCountry)-LEN(VALUE(CustomerID)))) for the random number to ALWAYS be the right length.

    total formula

    = CustomerCountry 
  & VALUE(CustomerID) 
  & LEFT(INT(RAND()*10^13);(13-LEN(CustomerCountry)-LEN(VALUE(CustomerID))))