+ Reply to Thread
Results 1 to 5 of 5

Insert a zero on the left of a string?

  1. #1
    Registered User
    Join Date
    01-23-2005
    Posts
    42

    Insert a zero on the left of a string?

    Hi Folks,
    I got some awesome help recently and this formula as a result
    =IF(LEFT(A3,1)="0",RIGHT(A3,LEN(A3)-1),A3)
    It worked great to strip the left most zero from a string of text numbers in the cells where the formula was applied.

    However, now I need to put the zero back in lol!

    So...how would I adjust the formula (or write a different one) to insert a zero as the first digit of a string of text digits?

    Many thanks!
    Last edited by dwest100; 01-22-2014 at 06:10 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Insert a zero on the left of a string?

    Assuming the string of numbers is text
    =0 & A3
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Insert a zero on the left of a string?

    Try this

    =IF(LEFT(A3,1)<>"0","0"&A3,A3)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    01-23-2005
    Posts
    42

    Re: Insert a zero on the left of a string?

    Well that was amazingly simple lol!
    Thanks to both of you!

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Insert a zero on the left of a string?

    Thank you for your feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to match left string
    By tuneloon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 11:32 AM
  2. Find string and save...insert another string
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 04:48 PM
  3. [SOLVED] Sum numbers left of a character from within a string
    By BarryTSL in forum Excel General
    Replies: 14
    Last Post: 04-17-2012, 01:11 PM
  4. [SOLVED] Remove left text string
    By Luc in Oxford in forum Excel General
    Replies: 2
    Last Post: 08-23-2005, 06:05 AM
  5. Left and right side of string?
    By Sasolini in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2005, 08:15 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1