Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Thanked 0 Times in 0 Posts

    Lookup Help (Excel 97)

    I have 2 columns of numbers which I need to match to use in a look up function.
    The first column has 10 digit numbers, the second has the same 10 digits but with spaces after the 3rd and 7th digits ie
    1230456789 and 123 0456 789.

    How do I either i) get rid of the spaces in the second column or ii) insert spaces into the first column?

    Have tried using "data" "text to columns" and then "cell 1 & cell 2 & cell 3". This works except where there are zeros, when it takes them out altogether leaving me with either a 9 or 8 digit number, rather than 10.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Lookup Help (Excel 97)

    Have the 10 digit numbers in column A been entered as text values? If so, you can use <code>SUBSTITUTE(B1," ","")</code> in a formula to remove the spaces from B1 and compare it to a value in column A.
    If they are numbers, you can use <code>VALUE(SUBSTITUTE(B1," ",""))</code>

Posting Permissions

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