Tags: columns, didnt, excel, filled, guess, hellomy, ismobile, ive, microsoft, msdn, number, numbers, software, spreadsheet, zero

Zero in front of number

On Microsoft » Microsoft Excel

3,563 words with 5 Comments; publish: Tue, 03 Jun 2008 22:10:00 GMT; (30666.41, « »)

Hello

My problem is, I've been given a spreadsheet where one of the columns is

Mobile numbers and the person who filled it in I guess didn't know that you

can format the column to 'text ' to keep the Zero in front of the number, so

he put a # in front of each number in order to keep the first 0.

I now want to remove all the #. But it's a very long list.

So I've put in FIND #0 and REPLACE 0 but when I click REPLACE ALL, it takes

away the # but also all the first Zeros!!. I've tried formatting the column

as Text, General, Custom but it doesn't work.

BTW why does Excel not like having a Zero in front of a number particularly

when you specifically type it in? If you didn't want it, you wouldn't type

it. right?

Anyhow so PLEASE could someone help me on this ?

JB

All Comments

Leave a comment...

  • 5 Comments
    • Hi,

      Try Replace '0

      The ' marks it in a way that Excel uses it as text and will not supress

      leading zeros.

      Udo

      #1; Tue, 03 Jun 2008 22:11:00 GMT
    • Highlight the column and format it as Text before you do the Find and

      Replace - you only need to Find #

      and Replace it with nothing or "".

      Alternatively, insert a new column next to the column of mobile phone

      numbers, then insert this formula:

      =RIGHT(D1,LEN(D1)-1)

      assuming the numbers are in column D. Copy this down by double-clicking

      the fill-handle (small black square in bottom right corner of the

      cursor). Click <copy> (as the cells are already highlighted) and Edit |

      Paste Special | Values (check) OK and <Esc>. Then you can delete the

      old column D.

      Hope this helps.

      Pete

      #2; Tue, 03 Jun 2008 22:12:00 GMT
    • yes

      "JB" <badspam.excel.todaysummary.com.awyway.com> escribi en el mensaje

      news:OpuQ2#UPGHA.2816.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > Hello

      > My problem is, I've been given a spreadsheet where one of the columns is

      > Mobile numbers and the person who filled it in I guess didn't know that

      you

      > can format the column to 'text ' to keep the Zero in front of the number,

      so

      > he put a # in front of each number in order to keep the first 0.

      > I now want to remove all the #. But it's a very long list.

      > So I've put in FIND #0 and REPLACE 0 but when I click REPLACE ALL, it

      takes

      > away the # but also all the first Zeros!!. I've tried formatting the

      column

      > as Text, General, Custom but it doesn't work.

      > BTW why does Excel not like having a Zero in front of a number

      particularly

      > when you specifically type it in? If you didn't want it, you wouldn't

      type

      > it. right?

      > Anyhow so PLEASE could someone help me on this ?

      > JB

      >

      #3; Tue, 03 Jun 2008 22:13:00 GMT
    • Thanks that worked!

      I also found that in Replace there's an option button which then shows

      format buttons. When I clicked the Format for Replace, chose Custom and

      typed 0############ that worked too.

      Your way was faster

      Thanks

      "Udo" <udo.baumfalk.excel.todaysummary.com.arcor.de> wrote in message

      news:1141231993.275391.306640.excel.todaysummary.com.u72g2000cwu.googlegr oups.com...

      > Hi,

      > Try Replace '0

      > The ' marks it in a way that Excel uses it as text and will not supress

      > leading zeros.

      > Udo

      >

      #4; Tue, 03 Jun 2008 22:14:00 GMT
    • Thanks for feeding back to the group.

      Pete

      #5; Tue, 03 Jun 2008 22:15:00 GMT