Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Format numbers

0

How do I format the excel column so that all front numbers become "+62"? For example: "082" become "+6282"? and also for number "0" after +62 disappears for example, "+62081" become "+6281"? 

For detail please take a look at the attached excel file and see the question of number 2 & 3 :)

Answer
Discuss

Answers

0
Selected Answer

Please try this formula in cell H7 of your worksheet.

=TEXT(VALUE(IF(LEFT($D7,3)="+62",MID($D7,4,20),$D7)),"""+62""0")

The backbone of this formula is in IF(LEFT($D7,3)="+62",MID($D7,4,20),$D7). This function removes "+62" if it is found in the original number. This is the part that would need to be expanded if you also encounter numbers starting with 0062 in column D.

The VALUE() function converts the string into a number, which removes any leading zeroes.

The outer TEXT() function, finally, converts the number back into a string (now without leading zeroes) and prefixes it with your country code.

Discuss

Discussion

Thank you so much for your valuable response. The function given by you is perfectly correct and it works. By the way what do you think about the question number 4? It says "there are 1000.000 data. Should i add the number cell untill 1.000.000 cell and add random data too? is there any option in Ms. Excel that provides random data? 
Fida_mutia (rep: 28) Dec 4, '18 at 6:22 pm
I don't like to answer more than one question per thread and your question No. 4 demonstrates the reason why. I don't understand what you mean by "random data", and if you have a million rows in your worksheet that would certainly be enough to warrant another question. Based on the formula you now have you would need to copy it to all rows. If you like to ask another question about that I could show you how to do it quickly (hint: use the Ribbon's GoTo function). There are ways of creating random data but I don't understand why you should want to add rubbish to your worksheet if you already have 1 million intelligent rows :-)
Variatus (rep: 4889) Dec 4, '18 at 9:03 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login