extract digits from a cell

[andywil]

hi i have a column filled with 13 digit numbers and i am looking to remove 5 digits from that number e.g 5020073417357 i need to remove 41735 (8 to 12) from every one in the column.

[TMS]

Assuming that's text rather than a number, this should work:

=LEFT(A2,8)&RIGHT(A2,1)


Regards, TMS

[Haseeb Avarakkan]

Also,

=REPLACE(A2,9,5,"")

[TMS]

@Haseeb: I did not know that. Thank you. Much neater.

Regards, TMS

[andywil]

thanks for the help guys but it is not leaving the numbers i need e.g 5020073356564 in need 35656 from this dont need the first 7 5020073 or the last number 4 only need 35656 from it thanks

[Kevin UK]

Hi andywil

Assuming your data is in column A, A1. in B1:

Please Login or Register  to view this content.

[Marcol]

Try this, it returns a number, not a string.

Please Login or Register  to view this content.

[Kevin UK]

@ Marcol

Yes forgot that!

Updated:

Please Login or Register  to view this content.

[andywil]

thanks guys worked a treat now how can insert the formula so it will do this for a column of 20 rows removing the 5 digits i require for each 13 digit number in the rows

[Kevin UK]

Hi andywil

Assuming Data is in A1:A20, put the formula in say B1 and copy down.

[andywil]

thanks for all the help guys great forum

[andywil]

can i replace the 13 digit number from the rows with the extracted 5 digit number into the original rows

[Kevin UK]

Hi andywil

Copy the column/cells with the extracted values, select the column/cells of the original data, paste special, paste values.