Formula to convert 4 digits number to digits number !!

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,444
Office Version
  1. 2007
Platform
  1. Windows
Hi everyone,
I'm pretty sure it's impossible but I'm gone ask anyway.
I wanted to now if some how a 4 digits number ( between 1000:7561 ) could be converted to a 3 digits number ( Between 100:999), and that I can with that conversion formula I could return to the original 4 digits number ?
Than you.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I am pretty sure if you remove significant digits from a number you can not regain the precision of the original number.
Do you have an algorithm that can be used in a formula?
 
Upvote 0
No.
What it is is that I created 2 excel system than one use 4 digits and the other 3 digits, the one that use 3 digits give me less selections to select from witch reduce enormously the error factor than the one with 4, so that's why I'm try to bring it down to 3 digits.
 
Upvote 0
then you'll need to show what you're doing. Please provide some examples, and explain how it is done from one system to the other.
 
Upvote 0
Than you awoohaw,
Example of my system will not help for what I'm asking here, I'm just trying to downsize 4 digits to 3, I need to find a solution, I don't know how but I will by dividing, misusing or else or using square root, digital root or using reference tables, etc...
 
Upvote 0
As @awoohaw has indicated, if you are limited to using base 10 (numerals 0..9 as the digits), then you'll run into difficulties trying to do this. However, as @Phuoc has suggested, if you are willing to accept that a "digit" can be something other than 0..9, then you can use the built-in BASE and DECIMAL functions to convert back and forth. Here is an example with Radix=22 showing 4-digits in one system (base 10) converted into 3-digits in another system (base 22), and then converted back into base 10 to recover the initial number.

I favor using a Radix=20 for this application.
Book2
ABCDEFG
14-digit3-digit (base 22)convert back
2100021A1000100021A1000
311002601100100121B1001
412002AC1200100221C1002
513002F21300100321D1003
614002JE1400100421E1004
715003241500100521F1005
8160036G1600100621G1006
917003B61700100721H1007
1018003FI1800100821I1008
1119003K81900100921J1009
12200042K2000101021K1010
13210047A2100101121L1011
1422004C0220010122201012
Sheet1
Cell Formulas
RangeFormula
A2:A68A2=SEQUENCE(67,,1000,100)
B2:B68,F2:F16B2=BASE(A2#,22)
C2:C68,G2:G16C2=DECIMAL(B2#,22)
E2:E16E2=SEQUENCE(15,,1000,1)
Dynamic array formulas.
 
Last edited:
Upvote 0
@Phuoc @KRice
If the OP's profile is correct then I don't think they would not be able to use BASE (or DECIMAL) as my understanding is those function were only introduced in Excel 2013.
 
Upvote 0
Totally missed that, Peter!...right you are. Well, if working in a different base works for the OP, then I suppose a UDF/VBA solution is an option, or a worksheet could be set up to perform the conversions:
MrExcel_20240217.xlsx
ABLMNOPQRSTUVWXYZAAAB
14-digit3-digit (base 20)400201Base20Base10Lookupsall text
210002A021002A02A02A01000000
310012A121012A12A12A11001111
410022A221022A22A22A21002222
510032A321032A32A32A31003333
610042A421042A42A42A41004444
710052A521052A52A52A51005555
810062A621062A62A62A61006666
910072A721072A72A72A71007777
1010082A821082A82A82A81008888
1110092A921092A92A92A91009999
1210102AA210102AA2AA2AA101010A10
1310112AB210112AB2AB2AB101111B11
1410122AC210122AD2AD2AD101212C12
1510132AD210132AD2AD2AD101212D12
1610142AE210142AE2AE2AE101414E14
1710152AF210152AF2AF2AF101515F15
1810162AG210162AG2AG2AG101616G16
1910172AH210172AH2AH2AH101717H17
2010182AI210182AI2AI2AI101818I18
2110192AJ210192AJ2AJ2AJ101919J19
2210202B021102B02B02B01020
Sheet1
Cell Formulas
RangeFormula
M1:O1M1=(20^{2,1,0})
A2:A6571A2=SEQUENCE(6570,,1000,1)
B2:B6571B2=BASE(A2#,20)
M2:M22M2=INT(A2/400)
N2:N22N2=INT((A2-M2*400)/20)
O2:O22O2=A2-M2*400-N2*20
P2:R22P2=VLOOKUP(M2,$Z$2:$AA$21,2)
S2:S22S2=P2&Q2&R2
U2:U22U2=LEFT(S2,1)
V2:V22V2=MID(S2,2,1)
W2:W22W2=RIGHT(S2,1)
X2:X22X2=SUMPRODUCT(VLOOKUP(U2:W2,$AA$2:$AB$21,2),{400,20,1})
Dynamic array formulas.

Clunky, but it works...although I would recommend a VBA solution. I see that @Rick Rothstein has already tackled that here:
VBA Code:
Function Base2Dec(BaseDigits As String, BaseNumber As Long) As Variant
    Dim X As Long, Z As Long, DigitVal As Variant, Power As Variant
    Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If Not UCase(BaseDigits) Like "*[!" & Left(PossibleDigits, BaseNumber) & "]*" Then
        For X = 0 To Len(BaseDigits) - 1
            DigitVal = UCase$(Mid(BaseDigits, Len(BaseDigits) - X, 1))
            DigitVal = InStr(PossibleDigits, DigitVal) - 1
            Power = 1
            For Z = 1 To X
              Power = CDec(Power) * BaseNumber
            Next
            Base2Dec = Base2Dec + DigitVal * Power
        Next
    Else
        Err.Raise 9999, , "Bad base digit specified"
    End If
End Function

...and here:
VBA Code:
Function Dec2Base(DecimalValue As Variant, Base As Long) As String
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  DecimalValue = CDec(DecimalValue)
  Do Until DecimalValue = 0
    Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
               Int(DecimalValue / Base) + 1, 1) & Dec2Base
    DecimalValue = Int(CDec(DecimalValue) / Base)
  Loop
End Function
 
Upvote 0
Wowww you guys are something else, I can't believe how far you go to help me and I really really appreciate it, but after seeing all your dedicated work I guess it will not be possible.
in column B I would need to have a base 10, 3 digits number in return.
Again thank you all very much for having taking the time to help me.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top