CODEWITHSUNDEEP
excelvbadigits
Cedric
Cedric

Reputation: 31

Reduce a Large Number to the necessary few digits

I just started using VBA for work and barely have any experience so any help/suggestions are highly appreciated!

I am working on an Automatic Report where i need to use the Number of a Device to find data belonging to it. Example 90902018436270200 is the fullNumber the required digits are 18436.

Also the numbers are displayed as 9.0902E+16 before i copy and change them from default to numbers.

The amount before the numbers always stay the same however the required number can rarely be only 4 digits long.

I tried to remove the first digits with:

If Left(Range("A5"), 1) = "9" Then
    Range("A5").Value = WorksheetFunction.Replace(Range("A5").Value, 1, 4, "")

However when using that the number actually becomes Larger which i assume is because of the exponential notation which i probably have to remove somehow.

This is the sheet i need to Filter

In the Screenshot you can see the large number is the one i edited with the formula.

The numbers are on the left side which indicate the name of the device to use =vlookup i need to filter out the unnessecary numbers or it wont recognize which row to take the data from. On Another sheet i have a cell the user can type the number of the device then it should find the data matching to the number of that device. However the formula =Vlookup wont recognize the required number in the raw/big number.

This is the code i am using so far to arrange the data to work with the excel formula.

Sub CorrectNames()

'replaces complicated names to easier ones

    Range("B3").Select
    ActiveCell.FormulaR1C1 = "10"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "20"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "50"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "100"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "1000"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "200"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "2000"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "500"
    ActiveCell.Offset(0, 1).Range("A1").Select

    'moves device digits to the left side and changes format

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    ActiveCell.Offset(0, -9).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.NumberFormat = "0."


End Sub

Sub ExposeDevicedigits()

If Left(Range("A5"), 1) = "9" Then
    Range("A5").Value = WorksheetFunction.Replace(Range("A5").Value, 1, 4, "")



End If

End Sub

The Data sheet where the filtered Results would go

Upvotes: 0

Views: 257

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4704

If 90902018436270200 is in A5 then =MID(text(A5,0),7,5) will return "18436" (The TEXT converts the value of A5 into a text string ignoring any weird formatting.

Upvotes: 1

Related Questions