Share via

Removing partial contents of a cell automatically

Anonymous
2011-07-01T13:57:11+00:00

I have a file whose first column looks like this:

      12345 - NameOfAgency

I want the column to have only the first 5 numbers.  There are 800 rows in the file and I don't want to manually delete all the data after the 5-digit numbers.  Is there a way I can do this automatically?

Thanks.

William

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-07-01T14:08:03+00:00

    You can do it automatically with a macro, but you can easily do it manually, too...

    Select your column. In the Data tab choose Text to Columns. Click the Delimited radio button and click Next.

    Check the Space checkbox and click Next. Select the column(s) to the right of the 5 digits and click the Do not import radio button. Click Finish. Won't take you 30 seconds...

    100+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-07-01T14:06:50+00:00

    Insert of column of formulas

    =LEFT(A2,5)

    or

    =VALUE(LEFT(A2,5))

    and copy down to match your data, then copy and pastespecial values, and either overwrite the initial data and then delete your inserted column, or delete it your intial column and leave the new column.

    Bernie

    60+ people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-07-01T14:20:23+00:00

    Thanks so much; this worked perfectly!

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-07-01T14:08:33+00:00

    Hi,

    Put this formula in a helper column and drag down. Once you've done that you can copy the numbers and paste special, paste values to overwrite the formula

    =LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-07-01T14:19:59+00:00

    Thanks.  This may work but the first two replies both work and are simpler.  Appreciate the response.

    0 comments