make the numbers (values) to 5 digit

symoin

New Member
Joined
Aug 7, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi, Greetings to All,
This is my first post.
I am looking for a formula or macro or Vba code for changing the values of the cell (content) to 5 digit numbers.
Attached is an excel for example. Column A has original Values and Column B has the desired values.

Thanks in Advance

TEST.xlsx
H
49
TEST
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
With XL2BB you need to first actually select the range that you want to show before you click 'Mini Sheet'.
 
Upvote 0
With XL2BB you need to first actually select the range that you want to show before you click 'Mini Sheet'.
ok, I have updated the Account details and created a new file from xl2bb.
TEST.xlsx
AB
1I HAVE TEXT WITH NUMBERS, Alpha and special charactersI NEED only numbers with 5 places
2805-180501
3805-180501
4805-180501
5805-180501
6805-180501
7805-180501
8805-180501
9805-180501
10811-181101
11811-181101
12811-181101
13811-181101
14811-181101
15811-181101
16811-181101
17805-280502
18805-280502
19835-183501
20835-183501
21835-183501
22835-283502
23835-283502
24835-383503
25835-383503
26835-383503
27805-280502
28805-280502
29805-280502
30805-280502
31837-183701
32837-183701
3371571500
3481181100
3581581500
3681381300
3773673600
3871271200
39805-3480534
40805-4180541
41805-4180541
42805-3480534
43805-3480534
44805-4180541
45805-54-ROUND80554
46805-54-SQUARE80554
47805-54-SQUARE80554
48705-54-2INCH70554
49705-54-2INCH70554
50805-5480554
51705-54-4FEET70554
52705-54-4FEET70554
53805-64-SQUARE80564
54805-64-ROUND80564
55814-12-POINT81412
56814-1-POINT81401
57805-7-POINT80507
58805-7-ROUND80507
59805-7-SQUARE80507
60805-8-STREET LIGHT 366/1280508
TEST
 
Upvote 0
See if this does what you want.

22 08 16.xlsm
AB
1DataResult
2805-180501
3805-180501
4805-180501
5805-180501
6805-180501
7805-180501
8805-180501
9805-180501
10811-181101
11811-181101
12811-181101
13811-181101
14811-181101
15811-181101
16811-181101
17805-280502
18805-280502
19835-183501
20835-183501
21835-183501
22835-283502
23835-283502
24835-383503
25835-383503
26835-383503
27805-280502
28805-280502
29805-280502
30805-280502
31837-183701
32837-183701
3371571500
3481181100
3581581500
3681381300
3773673600
3871271200
39805-3480534
40805-4180541
41805-4180541
42805-3480534
43805-3480534
44805-4180541
45805-54-ROUND80554
46805-54-SQUARE80554
47805-54-SQUARE80554
48705-54-2INCH70554
49705-54-2INCH70554
50805-5480554
51705-54-4FEET70554
52705-54-4FEET70554
53805-64-SQUARE80564
54805-64-ROUND80564
55814-12-POINT81412
56814-1-POINT81401
57805-7-POINT80507
58805-7-ROUND80507
59805-7-SQUARE80507
60805-8-STREET LIGHT 366/1280508
5 digits
Cell Formulas
RangeFormula
B2:B60B2=(LEFT(A2,FIND("-",A2&"-")-1)&TEXT(MID(A2&"-0-",5,FIND("-",A2&"-0-",5)-5),"00"))+0
 
Upvote 0
Thanks Peter,
working good, I just came across 2 other situations if you can include these also please.
TEST.xlsx
ABE
1I HAVE TEXT WITH NUMBERS, Alpha and special charactersI NEED only numbers with 5 places
2805-18050180501
3805-11180501805111
4805-1round80501#VALUE!
5805-18050180501
TEST
Cell Formulas
RangeFormula
E2:E5E2=(LEFT(A2,FIND("-",A2&"-")-1)&TEXT(MID(A2&"-0-",5,FIND("-",A2&"-0-",5)-5),"00"))+0
 
Upvote 0
:confused: I don't understand the logic of row 3. How do you get 80501 out of 805-111?

In any case it might require switching to vba.
 
Upvote 0
Try this

Book1
AB
2805-180501
3805-11180511
4805-1round80501
573673600
Sheet1
Cell Formulas
RangeFormula
B2:B5B2=LEFT(SUBSTITUTE(A2&"0000","-",IF(ISNUMBER(--MID(A2,6,1)),"",0)),5)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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