separating digits

Go to the data tab, click text to columns. Fixed width, add the columns between each number

Heck of a lot easier than using a formula
 
Tom, here is one way to separate your original 1632 into separate digits using formulas...

1706453585205.png

Your original number is in cell B40.
To get the "1" digit - the formula I used is =TRUNC(B40/1000)
This divides 1632 by 1000 to get 1.632. The Truncate function eliminates the decimal portion (.632), leaving the "1"

To get the next digit ("6"), you do something similar. That formula is =TRUNC(B40/100) - (C40*10)
This divides 1632 by 100 to get 16.32. Then truncate it to leave 16. Then subtracts the answer from C40 (the "1") times 10
1632/100 -> 16.32 -> 16 -> subtract the 10 leaves 6

Same ideas for the "3" and the "2". It just keeps getting one calculation longer each time. For the "3", you have to subtract 100 and 60 from 163.
For the "2" you don't need to truncate anymore, you simply subtract 1000, and 600 and 30 from your original 1632

Not sure if this helps any.
 
MD Automation said:
Tom, here is one way to separate your original 1632 into separate digits using formulas...

View attachment 2569750

Your original number is in cell B40.
To get the "1" digit - the formula I used is =TRUNC(B40/1000)
This divides 1632 by 1000 to get 1.632. The Truncate function eliminates the decimal portion (.632), leaving the "1"

To get the next digit ("6"), you do something similar. That formula is =TRUNC(B40/100) - (C40*10)
This divides 1632 by 100 to get 16.32. Then truncate it to leave 16. Then subtracts the answer from C40 (the "1") times 10
1632/100 -> 16.32 -> 16 -> subtract the 10 leaves 6

Same ideas for the "3" and the "2". It just keeps getting one calculation longer each time. For the "3", you have to subtract 100 and 60 from 163.
For the "2" you don't need to truncate anymore, you simply subtract 1000, and 600 and 30 from your original 1632

Not sure if this helps any.
Click to expand...
Truncate was the function I was looking for, but that by itself isn't working
 
ptonsparky said:
Truncate was the function I was looking for, but that by itself isn't working
Click to expand...
Does the formula approach I posted above help? You say the TRUNC function is not working by itself. Not quite sure what you mean by that.

Did you see the formulas I used for cells C40, D40, E40 and F40 in my post above.
 
Use the MID function.
In A1 type 1625
Curse over to C3
Hit FX and type MID
Function menu will pop up. Hit ok
For the Text section choose A1
For start number, type 1
For number of characters, type 1
Hit ok
Then repeat for next cell D1 and so on.

Then go to cell A2 and type in number.
High light cell C1-F1 and drag down.

Be sure to format you first four result cells to number with zero decimal places.
This will give you the basic info to strat. Then dial in per color, tolerance etc.

FYI I always skip one cell to start as a buffer. Just easier for me.
 

Attachments

  • Screenshot_20240128-110152.png
    Screenshot_20240128-110152.png
    493 KB · Views: 6
  • Screenshot_20240128-110219.png
    Screenshot_20240128-110219.png
    432.2 KB · Views: 6
  • Screenshot_20240128-110240.png
    Screenshot_20240128-110240.png
    524.3 KB · Views: 5
  • Screenshot_20240128-111157.png
    Screenshot_20240128-111157.png
    724.8 KB · Views: 5
  • Screenshot_20240128-111216.png
    Screenshot_20240128-111216.png
    553.8 KB · Views: 6
  • Screenshot_20240128-111233.png
    Screenshot_20240128-111233.png
    551.3 KB · Views: 6
  • Screenshot_20240128-111250.png
    Screenshot_20240128-111250.png
    461.9 KB · Views: 6
Last edited:
MD Automation said:
Does the formula approach I posted above help? You say the TRUNC function is not working by itself. Not quite sure what you mean by that.

Did you see the formulas I used for cells C40, D40, E40 and F40 in my post above.
Click to expand...
Yes, got it to work for the range of 1100 to 9900 but not for outside of that range. IIRC. My memory is as short as my small digit. Makes my eyes cross thinking of what I need to do for 99 mega ohm.
 
ptonsparky said:
What I want to do is separate the number into color codes for a resistor. 1st,, 2nd, and multiplier for a 4 band.
1600 would be a better #. (1, 6, 100)
Click to expand...
None of the examples so far really do what you want.
can you give more examples of the numbers your using?
 
Hv&Lv said:
None of the examples so far really do what you want.
can you give more examples of the numbers your using?
Click to expand...
Pretty sure what I supplied is what he ask for. He ask for a way to take the four digit number and put each in it's own cell in order.
From there he can assign a multiplier function.

Agreed it may be what he want as a end result.
 
Tulsa Electrician said:
Pretty sure what I supplied is what he ask for. He ask for a way to take the four digit number and put each in it's own cell in order.
From there he can assign a multiplier function.

Agreed it may be what he want as a end result.
Click to expand...
Thats what my post #2 does without formulas Using data tab and text to columns.

in post #3, last sentence he's asking for something different.
 
Insert known values @ RED text
Known Value1120=MID($B$2,1,1)=MID($B$2,2,1)=MID($B$2,3,1)=MID($B$2,4,1)=MID($B$2,5,1)=MID($B$2,6,1)=MID($B$2,7,1)=MID($B$2,8,1)
Multiplier if Above is <10000.01=C2*1=D2*1=E2*1
Tolerance0.1=VLOOKUP(C3,D14:E23,2,1)=VLOOKUP(D3,D14:E23,2,1)=VLOOKUP(E3,D14:E23,2,1)=VLOOKUP(H4,J12:L23,3,2)=VLOOKUP(B4,A14:B18,2,1)=IF(I4>0,I4,B3)=COUNTIF(F2:O2,0)
Band1st2nd3rd4th Multiplier5th Tolerance
 
You must log in or register to reply here.
Top