Apr 12, 2011

How to Remove Line Numbers in Excel Column

It explains how you can remove line numbers and get proper data in a excel column. Suppose you have data in following format in A column:

1. First Data

2. Second Data

3. Third Data

4. Fourth Data

5. Fifth Data

You have to remove line numbers and get content in another column. See following screen-shot:

excel remove line numbers

For this format, select first row B cell and paste following formula(assuming your data in column A starts with first row)

=RIGHT(A1,LEN(A1)-FIND(".",A1))

Now, Drag the cell to apply formula for another rows. You will get the data excluding line numbers.

If your format is different then replace dot(.) with proper separator in the formula.

Suppose your data is in following format:

1) First Data

2) Second Data

3) Third Data

4) Fourth Data

5) Fifth Data

Then formula will be

=RIGHT(A1,LEN(A1)-FIND(")",A1))

Let us take another example. Suppose your data is in following format:

1:-First Data

2:-Second Data

3:-Third Data

4:-Fourth Data

5:-Fifth Data

then use following formula

=RIGHT(A1,LEN(A1)-FIND(":-",A1)-1)

Suppose you have to remove line numbers from A column permanently,

> Copy the excluded line number column(in our case column B)

> Select A column > Right click > Paste Special

> Select Values and Click OK.

It will overwrite values.

Hope, It helps.