Page 1 of 2 12 LastLast
Results 1 to 10 of 18

adding zero at the start of number

This is a discussion on adding zero at the start of number within the Excel Questions forums, part of the Question Forums category; Dear all, I have phone numbers imported from outlook in csv format. Now these numbers are like 2782512512, i want ...

  1. #1
    Board Regular vipulgos's Avatar
    Join Date
    Aug 2002
    Location
    India
    Posts
    262

    Default adding zero at the start of number

    Dear all,
    I have phone numbers imported from outlook in csv format. Now these numbers are like 2782512512, i want to add Zero at the start of this number. So, it will become 02782512512. How can i do this. Find and Replace method doesn't work.
    please guide me.
    Last edited by vipulgos; Jan 12th, 2008 at 06:04 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2008
    Posts
    84

    Default Re: adding zero at the start of number

    Your numbers are probably stores as numbers, if you still want them stored as numbers you could use custom number formatting.
    Select Format -> Cell -> Number -> Custom
    and enter this format: 00000000000
    this makes your number display with 11 digits and leading zeros.

    If you want your numbers stored as text. With your numbers in column A, but this inn B1 and copy down:
    =TEXT(A1,"00000000000")
    or this
    ="0"&A1

    the latest formula even works for number with less or more than 10 digits after the zero, and is probably your best choice.

    You can the use copy and paste special values to convert eliminate the formulas, and then copy column B to column A, to replace the original number.

  3. #3
    Board Regular vipulgos's Avatar
    Join Date
    Aug 2002
    Location
    India
    Posts
    262

    Default Re: adding zero at the start of number

    But in both case:
    Few numbers are already started with 0278, 50% of number starts with 278. So, applying format or formula to the numbers which starts from 278 will then have to re-enter in the B column by picking one by one cells. This is a real headache.
    Also this problem is for business phone numbers, home phone numbers, etc. So, applying formula to all is not possible. I require something like find 278 and replace it with 0278. I think this is only the remedy

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    84

    Default Re: adding zero at the start of number

    I still think we can find a formula that works for you.
    Could you post some representative examples of some of the numbers you are trying to convert, and what result you want for each of these.
    Then I will try to come up with a formula.

  5. #5
    Board Regular vipulgos's Avatar
    Join Date
    Aug 2002
    Location
    India
    Posts
    262

    Default Re: adding zero at the start of number

    Hello dear this is the data you want to see. These data after compilation i want to send it back to outlook and then to my mobile device.
    ******** language="JavaScript" ************************************************************************>
    Microsoft Excel - for mobile-4.csv___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    1
    Middle NameLast NameSuffixCompanyJob TitleBusiness StreetBusiness CityHome StreetBusiness FaxBusiness PhoneBusiness Phone 2Home PhoneHome Phone 2Mobile Phone
    2
    D.Amin 94263451342782432139 2782423554 9824740820
    3
    D.Gohil-IOB a-bad-26582356079-26587390 /91-padmanabhan 2782525740 9426920907
    4
    P.Bhatt 2782203619 9898982902
    5
    2782421029 002782445577 /2449138-jagdish jajadia 9426819168 /9898985577-jagdish jajadia
    6
    Ad. 2782519883+091 (00278) 2424322278242432227825678182785607719824209330 hiteshbhai
    7
    T.Rathod 9825803808
    8
    BhangariScrap pp-2427042 9825207127
    9
    Gosalia 9428993622 9824286243
    10
    Aastikbhai 9427755322
    11
    inquiry 66666666
    12
    2782413211 9328930959
    13
    9825505089 -nilaben2540218 , 2540232 002782571876 -thakur 9879042481 -ashok josi
    14
    oil-7300-5/8/06 2782511934
    15
    2782431367 2782570686 9825082057
    16
    9327322272 -aharisaheb2782562509 02842-236001 -alang 9377422272 -son
    17
    Ahemdabad civil 9.17922E+11
    18
    Duccan 2783091179 batibhai-9426941459
    19
    204, Toran Apts.,
    Asopalav society,
    Tadvadi, Rander road,
    Surat 2782414721 0261-6556615 /2567280 9825045360
    20
    Shah 2782201098 9426461302
    21
    Ajay Shukla 9824221168
    22
    VoraUiic 9825553052
    23
    GSFC 94272002780 --ajaybhai 27825602787 9426256487 - daughter
    24
    Sheth 9426210444
    25
    Er. 9374524848 2782567606 9426968011
    26
    Thakker 9322107123 2782566309 9426207123
    27
    Section-Vishwakarma 2782520751 2471065 /2541007
    28
    9909606060
    for mobile-4

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,974

    Default Re: adding zero at the start of number

    When importing the file into Excel, format the relevant columns as 'Text'. You're given an opportunity to do so, probably during Step 3, where you can click to choose your column or columns. Then select 'Text' as your format.

    Hope this helps!

  7. #7
    Board Regular vipulgos's Avatar
    Join Date
    Aug 2002
    Location
    India
    Posts
    262

    Default Re: adding zero at the start of number

    But the data in outlook itself starts from 278, in place of 0278. That's why i want to import to excel, so that i can edit in excel (278 to 0278).
    After editing I'll send these data back to outlook and then mobile device
    Last edited by vipulgos; Jan 12th, 2008 at 08:02 AM.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,974

    Default Re: adding zero at the start of number

    For clarity, can you post a small sample of the data as it starts out, the way you'd like it to look, and the rules involved.

    Note: If the data you've already posted is real data, I'd suggest you remove it (or if you're not able, ask one of the moderators to have it removed) and post fake data instead.

  9. #9
    Board Regular vipulgos's Avatar
    Join Date
    Aug 2002
    Location
    India
    Posts
    262

    Default Re: adding zero at the start of number

    The data I've posted is the real data and there is nothing secret about it. so, please be continue.
    I want the numbers which starts like 278222222 are to be edited as 0278222222. Also, it should be remain in number or general format. So that while exporting these data back to outlook, it should be go back to 278222222,
    thanks

  10. #10
    Board Regular
    Join Date
    Jan 2008
    Posts
    84

    Default Re: adding zero at the start of number

    Try this in Q2:

    =IF(LEFT(K2;3)="278";"0"&K2;K2)

    and copy to the range: Q2 to V30
    This puts a zero in front af all numbers starting with 278, and leaves all other untouched.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com