Question to GillesD or PAB

[Grandmaster]

Hi,

Do you know of any function in excel that could make a conversion of Pick 3 and Pick 4 numbers?

Example:

Original numbers

Column A B C
Row 1 4 5

I want the same numbers but only in 1 column instead of 3,

Column A
Row 145


I have this problem when I downloaded Pick 3 and Pick 4 history, every number is in a single column and I need all the numbers in a single column..

Regards

 

[GillesD]

From 3 to 1 column

If your data is set up like 1 in cell A2, 4 in cell B2 and 5 in cell C2, you can get 145 in cell D2 by entering the formula =100*A2+10*B2+C2.

Then, copy the formula for all lines where it may apply. Finally convert the formulas into values with the Paste Special command and then, you can remove columns A, B and C without affecting your data.

 

[Grandmaster]

Thank you GillesD for your help , it saved me a lot of hours editing,

For Pick 4 I just adjust your original formula and it works perfectly!

Regards

 

[PAB]

Hi Grandmaster,

Here are Two Other Ways of Achieving what you want.
If your Data is Setup like 1 in Cell A2, 4 in Cell B2 and 5 in Cell C2, you can get 145 in Cell D2 by Entering Either of the Following Formulas ...

=--CONCATENATE(A2,B2,C2)

... OR ...

=--(A2&B2&C2)

... and then, as GillesD Quite Rightly said, Copy the Formula Down for ALL Lines where it May Apply. Finally, Convert the Formulas into Values with the Paste Special Command, this Enables you to Remove Columns A, B and C Without Affecting your Data.

Good Luck.
All the Best.
PAB

 

[Grandmaster]

Hi,

Thank you PAB for your help ,

Very nice formula, it also works for Pick 4, or more adding cells to the function..

2 completely different approach (GillesD and you) to same problem with same results

Regards