Forum home » Delegate support and help forum » Microsoft Excel Training and help » MS Excel
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
MS Excel
Resolved · High Priority · Version 2007
Reply Mon 10 Oct 2011, 12:47Delegate Novy said...
Novy has attended:
Excel Advanced course
MS Excel
I have a column with the following numbers
41001000
42500000
42502101
43001000
43001005
43101000
44014000
45001001
45001008
51031000
51070000
51091000
51101100
51121000
51121010
51251017
51431000
51431002
52991000
52991009
55300000
55301000
55411000
55411050
55411100
55481000
56011002
63101058
64000000
64100003
64111002
71001000
71041000
71042000
71050000
81001000
81121004
81201000
81301001
81331000
81411000
81431000
81431020
81431030
81431060
81441000
81441100
81451100
81491011
81491014
82001016
82001020
82102000
82202000
82401000
82401010
82501000
82501006
83001000
83061000
83061010
83101300
83101301
83111100
83201000
83221000
83221010
83303000
83411000
83421000
83901010
84001000
84101000
84501010
85001000
85001100
85211200
85221010
85301000
85321000
85331000
85331007
85411000
85421030
85431000
85441000
85451000
85501000
85511000
85561000
85690101
85691000
85691030
85691070
88101000
88501000
88501005
89001000
Each number has eight characters e,g, 41001000 , 51002000
What we would like to do is to be able to change all the numbers beginning with 4 to be replaced by 40000000, 5 to be replaced by 50000000
What function would help us achieve this result. Possibly IF (nested IF), but how will it be structured?
Thanks
For upcoming training course dates see: Pricing & availability
RE: MS Excel
Hello Novy,
Hope you enjoyed your Microsoft Advanced course with Best STL.
Thank you for your question regarding the use of multiple IFs.
I have created a spreadsheet showing you how to use the IF function together with the LEFT function which handles the issue you have mentioned.
The formula is as follows:
=IF(LEFT(A1,1)="4",40000000,IF(LEFT(A1,1)="5",50000000,IF(LEFT(A1,1)="6",60000000,IF(LEFT(A1,1)="7",70000000,IF(LEFT(A1,1)="8",80000000,"")))))
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
MultipleIFs.xlsxRE: MS Excel
Hello Novy,
There is another way to solve your problem which is a much simpler formula. It is as follows:
=ROUNDDOWN(A1,-7)
Try this... don't forget to copy all cells with the formula and then paste values which will replace the formula with the value.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Mon 17 Oct 2011: Automatically marked as resolved.
|